{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prerequirement\n",
    "### 1. Download data\n",
    "Dataset is derived from Fannie Mae’s [Single-Family Loan Performance Data](http://www.fanniemae.com/portal/funding-the-market/data/loan-performance-data.html) with all rights reserved by Fannie Mae. Refer to these [instructions](https://github.com/NVIDIA/spark-rapids-examples/blob/branch-24.12/docs/get-started/xgboost-examples/dataset/mortgage.md) to download the dataset.\n",
    "\n",
    "### 2. Download needed jars\n",
    "* [rapids-4-spark_2.12-25.04.0.jar](https://repo1.maven.org/maven2/com/nvidia/rapids-4-spark_2.12/25.04.0/rapids-4-spark_2.12-25.04.0.jar)\n",
    "\n",
    "\n",
    "### 3. Start Spark Standalone\n",
    "Before running the script, please setup Spark standalone mode\n",
    "\n",
    "### 4. Add ENV\n",
    "```\n",
    "$ export SPARK_JARS=rapids-4-spark_2.12-25.04.0.jar\n",
    "$ export PYSPARK_DRIVER_PYTHON=jupyter \n",
    "$ export PYSPARK_DRIVER_PYTHON_OPTS=notebook\n",
    "```\n",
    "\n",
    "### 5. Start Jupyter Notebook with plugin config\n",
    "\n",
    "```\n",
    "$ pyspark --master ${SPARK_MASTER}            \\\n",
    "--jars ${SPARK_JARS}                \\\n",
    "--conf spark.plugins=com.nvidia.spark.SQLPlugin \\\n",
    "--conf spark.rapids.sql.incompatibleDateFormats.enabled=true \\\n",
    "--conf spark.rapids.sql.csv.read.double.enabled=true \\\n",
    "--py-files ${SPARK_PY_FILES}\n",
    "```\n",
    "\n",
    "## Import Libs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "import os\n",
    "from pyspark import broadcast\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql.types import *\n",
    "from pyspark.sql.window import Window\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Spark Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = (SparkSession\n",
    "    .builder\n",
    "    .appName(\"MortgageETL\")\n",
    "    .getOrCreate())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Function Define\n",
    "### 1. Define the constants\n",
    "\n",
    "* Define input file schema (Performance and Acquisition)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# File schema\n",
    "_csv_raw_schema = StructType([\n",
    "      StructField(\"reference_pool_id\", StringType()),\n",
    "      StructField(\"loan_id\", LongType()),\n",
    "      StructField(\"monthly_reporting_period\", StringType()),\n",
    "      StructField(\"orig_channel\", StringType()),\n",
    "      StructField(\"seller_name\", StringType()),\n",
    "      StructField(\"servicer\", StringType()),\n",
    "      StructField(\"master_servicer\", StringType()),\n",
    "      StructField(\"orig_interest_rate\", DoubleType()),\n",
    "      StructField(\"interest_rate\", DoubleType()),\n",
    "      StructField(\"orig_upb\", DoubleType()),\n",
    "      StructField(\"upb_at_issuance\", StringType()),\n",
    "      StructField(\"current_actual_upb\", DoubleType()),\n",
    "      StructField(\"orig_loan_term\", IntegerType()),\n",
    "      StructField(\"orig_date\", StringType()),\n",
    "      StructField(\"first_pay_date\", StringType()),    \n",
    "      StructField(\"loan_age\", DoubleType()),\n",
    "      StructField(\"remaining_months_to_legal_maturity\", DoubleType()),\n",
    "      StructField(\"adj_remaining_months_to_maturity\", DoubleType()),\n",
    "      StructField(\"maturity_date\", StringType()),\n",
    "      StructField(\"orig_ltv\", DoubleType()),\n",
    "      StructField(\"orig_cltv\", DoubleType()),\n",
    "      StructField(\"num_borrowers\", DoubleType()),\n",
    "      StructField(\"dti\", DoubleType()),\n",
    "      StructField(\"borrower_credit_score\", DoubleType()),\n",
    "      StructField(\"coborrow_credit_score\", DoubleType()),\n",
    "      StructField(\"first_home_buyer\", StringType()),\n",
    "      StructField(\"loan_purpose\", StringType()),\n",
    "      StructField(\"property_type\", StringType()),\n",
    "      StructField(\"num_units\", IntegerType()),\n",
    "      StructField(\"occupancy_status\", StringType()),\n",
    "      StructField(\"property_state\", StringType()),\n",
    "      StructField(\"msa\", DoubleType()),\n",
    "      StructField(\"zip\", IntegerType()),\n",
    "      StructField(\"mortgage_insurance_percent\", DoubleType()),\n",
    "      StructField(\"product_type\", StringType()),\n",
    "      StructField(\"prepayment_penalty_indicator\", StringType()),\n",
    "      StructField(\"interest_only_loan_indicator\", StringType()),\n",
    "      StructField(\"interest_only_first_principal_and_interest_payment_date\", StringType()),\n",
    "      StructField(\"months_to_amortization\", StringType()),\n",
    "      StructField(\"current_loan_delinquency_status\", IntegerType()),\n",
    "      StructField(\"loan_payment_history\", StringType()),\n",
    "      StructField(\"mod_flag\", StringType()),\n",
    "      StructField(\"mortgage_insurance_cancellation_indicator\", StringType()),\n",
    "      StructField(\"zero_balance_code\", StringType()),\n",
    "      StructField(\"zero_balance_effective_date\", StringType()),\n",
    "      StructField(\"upb_at_the_time_of_removal\", StringType()),\n",
    "      StructField(\"repurchase_date\", StringType()),\n",
    "      StructField(\"scheduled_principal_current\", StringType()),\n",
    "      StructField(\"total_principal_current\", StringType()),\n",
    "      StructField(\"unscheduled_principal_current\", StringType()),\n",
    "      StructField(\"last_paid_installment_date\", StringType()),\n",
    "      StructField(\"foreclosed_after\", StringType()),\n",
    "      StructField(\"disposition_date\", StringType()),\n",
    "      StructField(\"foreclosure_costs\", DoubleType()),\n",
    "      StructField(\"prop_preservation_and_repair_costs\", DoubleType()),\n",
    "      StructField(\"asset_recovery_costs\", DoubleType()),\n",
    "      StructField(\"misc_holding_expenses\", DoubleType()),\n",
    "      StructField(\"holding_taxes\", DoubleType()),\n",
    "      StructField(\"net_sale_proceeds\", DoubleType()),\n",
    "      StructField(\"credit_enhancement_proceeds\", DoubleType()),\n",
    "      StructField(\"repurchase_make_whole_proceeds\", StringType()),\n",
    "      StructField(\"other_foreclosure_proceeds\", DoubleType()),\n",
    "      StructField(\"non_interest_bearing_upb\", DoubleType()),\n",
    "      StructField(\"principal_forgiveness_upb\", StringType()),\n",
    "      StructField(\"original_list_start_date\", StringType()),\n",
    "      StructField(\"original_list_price\", StringType()),\n",
    "      StructField(\"current_list_start_date\", StringType()),\n",
    "      StructField(\"current_list_price\", StringType()),\n",
    "      StructField(\"borrower_credit_score_at_issuance\", StringType()),\n",
    "      StructField(\"co-borrower_credit_score_at_issuance\", StringType()),\n",
    "      StructField(\"borrower_credit_score_current\", StringType()),\n",
    "      StructField(\"co-Borrower_credit_score_current\", StringType()),\n",
    "      StructField(\"mortgage_insurance_type\", DoubleType()),\n",
    "      StructField(\"servicing_activity_indicator\", StringType()),\n",
    "      StructField(\"current_period_modification_loss_amount\", StringType()),\n",
    "      StructField(\"cumulative_modification_loss_amount\", StringType()),\n",
    "      StructField(\"current_period_credit_event_net_gain_or_loss\", StringType()),\n",
    "      StructField(\"cumulative_credit_event_net_gain_or_loss\", StringType()),\n",
    "      StructField(\"homeready_program_indicator\", StringType()),\n",
    "      StructField(\"foreclosure_principal_write_off_amount\", StringType()),\n",
    "      StructField(\"relocation_mortgage_indicator\", StringType()),\n",
    "      StructField(\"zero_balance_code_change_date\", StringType()),\n",
    "      StructField(\"loan_holdback_indicator\", StringType()),\n",
    "      StructField(\"loan_holdback_effective_date\", StringType()),\n",
    "      StructField(\"delinquent_accrued_interest\", StringType()),\n",
    "      StructField(\"property_valuation_method\", StringType()),\n",
    "      StructField(\"high_balance_loan_indicator\", StringType()),\n",
    "      StructField(\"arm_initial_fixed-rate_period_lt_5_yr_indicator\", StringType()),\n",
    "      StructField(\"arm_product_type\", StringType()),\n",
    "      StructField(\"initial_fixed-rate_period\", StringType()),\n",
    "      StructField(\"interest_rate_adjustment_frequency\", StringType()),\n",
    "      StructField(\"next_interest_rate_adjustment_date\", StringType()),\n",
    "      StructField(\"next_payment_change_date\", StringType()),\n",
    "      StructField(\"index\", StringType()),\n",
    "      StructField(\"arm_cap_structure\", StringType()),\n",
    "      StructField(\"initial_interest_rate_cap_up_percent\", StringType()),\n",
    "      StructField(\"periodic_interest_rate_cap_up_percent\", StringType()),\n",
    "      StructField(\"lifetime_interest_rate_cap_up_percent\", StringType()),\n",
    "      StructField(\"mortgage_margin\", StringType()),\n",
    "      StructField(\"arm_balloon_indicator\", StringType()),\n",
    "      StructField(\"arm_plan_number\", StringType()),\n",
    "      StructField(\"borrower_assistance_plan\", StringType()),\n",
    "      StructField(\"hltv_refinance_option_indicator\", StringType()),\n",
    "      StructField(\"deal_name\", StringType()),\n",
    "      StructField(\"repurchase_make_whole_proceeds_flag\", StringType()),\n",
    "      StructField(\"alternative_delinquency_resolution\", StringType()),\n",
    "      StructField(\"alternative_delinquency_resolution_count\", StringType()),\n",
    "      StructField(\"total_deferral_amount\", StringType())\n",
    "      ])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Define seller name mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# name mappings\n",
    "_name_mapping = [\n",
    "        (\"WITMER FUNDING, LLC\", \"Witmer\"),\n",
    "        (\"WELLS FARGO CREDIT RISK TRANSFER SECURITIES TRUST 2015\", \"Wells Fargo\"),\n",
    "        (\"WELLS FARGO BANK,  NA\" , \"Wells Fargo\"),\n",
    "        (\"WELLS FARGO BANK, N.A.\" , \"Wells Fargo\"),\n",
    "        (\"WELLS FARGO BANK, NA\" , \"Wells Fargo\"),\n",
    "        (\"USAA FEDERAL SAVINGS BANK\" , \"USAA\"),\n",
    "        (\"UNITED SHORE FINANCIAL SERVICES, LLC D\\\\/B\\\\/A UNITED WHOLESALE MORTGAGE\" , \"United Seq(e\"),\n",
    "        (\"U.S. BANK N.A.\" , \"US Bank\"),\n",
    "        (\"SUNTRUST MORTGAGE INC.\" , \"Suntrust\"),\n",
    "        (\"STONEGATE MORTGAGE CORPORATION\" , \"Stonegate Mortgage\"),\n",
    "        (\"STEARNS LENDING, LLC\" , \"Stearns Lending\"),\n",
    "        (\"STEARNS LENDING, INC.\" , \"Stearns Lending\"),\n",
    "        (\"SIERRA PACIFIC MORTGAGE COMPANY, INC.\" , \"Sierra Pacific Mortgage\"),\n",
    "        (\"REGIONS BANK\" , \"Regions\"),\n",
    "        (\"RBC MORTGAGE COMPANY\" , \"RBC\"),\n",
    "        (\"QUICKEN LOANS INC.\" , \"Quicken Loans\"),\n",
    "        (\"PULTE MORTGAGE, L.L.C.\" , \"Pulte Mortgage\"),\n",
    "        (\"PROVIDENT FUNDING ASSOCIATES, L.P.\" , \"Provident Funding\"),\n",
    "        (\"PROSPECT MORTGAGE, LLC\" , \"Prospect Mortgage\"),\n",
    "        (\"PRINCIPAL RESIDENTIAL MORTGAGE CAPITAL RESOURCES, LLC\" , \"Principal Residential\"),\n",
    "        (\"PNC BANK, N.A.\" , \"PNC\"),\n",
    "        (\"PMT CREDIT RISK TRANSFER TRUST 2015-2\" , \"PennyMac\"),\n",
    "        (\"PHH MORTGAGE CORPORATION\" , \"PHH Mortgage\"),\n",
    "        (\"PENNYMAC CORP.\" , \"PennyMac\"),\n",
    "        (\"PACIFIC UNION FINANCIAL, LLC\" , \"Other\"),\n",
    "        (\"OTHER\" , \"Other\"),\n",
    "        (\"NYCB MORTGAGE COMPANY, LLC\" , \"NYCB\"),\n",
    "        (\"NEW YORK COMMUNITY BANK\" , \"NYCB\"),\n",
    "        (\"NETBANK FUNDING SERVICES\" , \"Netbank\"),\n",
    "        (\"NATIONSTAR MORTGAGE, LLC\" , \"Nationstar Mortgage\"),\n",
    "        (\"METLIFE BANK, NA\" , \"Metlife\"),\n",
    "        (\"LOANDEPOT.COM, LLC\" , \"LoanDepot.com\"),\n",
    "        (\"J.P. MORGAN MADISON AVENUE SECURITIES TRUST, SERIES 2015-1\" , \"JP Morgan Chase\"),\n",
    "        (\"J.P. MORGAN MADISON AVENUE SECURITIES TRUST, SERIES 2014-1\" , \"JP Morgan Chase\"),\n",
    "        (\"JPMORGAN CHASE BANK, NATIONAL ASSOCIATION\" , \"JP Morgan Chase\"),\n",
    "        (\"JPMORGAN CHASE BANK, NA\" , \"JP Morgan Chase\"),\n",
    "        (\"JP MORGAN CHASE BANK, NA\" , \"JP Morgan Chase\"),\n",
    "        (\"IRWIN MORTGAGE, CORPORATION\" , \"Irwin Mortgage\"),\n",
    "        (\"IMPAC MORTGAGE CORP.\" , \"Impac Mortgage\"),\n",
    "        (\"HSBC BANK USA, NATIONAL ASSOCIATION\" , \"HSBC\"),\n",
    "        (\"HOMEWARD RESIDENTIAL, INC.\" , \"Homeward Mortgage\"),\n",
    "        (\"HOMESTREET BANK\" , \"Other\"),\n",
    "        (\"HOMEBRIDGE FINANCIAL SERVICES, INC.\" , \"HomeBridge\"),\n",
    "        (\"HARWOOD STREET FUNDING I, LLC\" , \"Harwood Mortgage\"),\n",
    "        (\"GUILD MORTGAGE COMPANY\" , \"Guild Mortgage\"),\n",
    "        (\"GMAC MORTGAGE, LLC (USAA FEDERAL SAVINGS BANK)\" , \"GMAC\"),\n",
    "        (\"GMAC MORTGAGE, LLC\" , \"GMAC\"),\n",
    "        (\"GMAC (USAA)\" , \"GMAC\"),\n",
    "        (\"FREMONT BANK\" , \"Fremont Bank\"),\n",
    "        (\"FREEDOM MORTGAGE CORP.\" , \"Freedom Mortgage\"),\n",
    "        (\"FRANKLIN AMERICAN MORTGAGE COMPANY\" , \"Franklin America\"),\n",
    "        (\"FLEET NATIONAL BANK\" , \"Fleet National\"),\n",
    "        (\"FLAGSTAR CAPITAL MARKETS CORPORATION\" , \"Flagstar Bank\"),\n",
    "        (\"FLAGSTAR BANK, FSB\" , \"Flagstar Bank\"),\n",
    "        (\"FIRST TENNESSEE BANK NATIONAL ASSOCIATION\" , \"Other\"),\n",
    "        (\"FIFTH THIRD BANK\" , \"Fifth Third Bank\"),\n",
    "        (\"FEDERAL HOME LOAN BANK OF CHICAGO\" , \"Fedral Home of Chicago\"),\n",
    "        (\"FDIC, RECEIVER, INDYMAC FEDERAL BANK FSB\" , \"FDIC\"),\n",
    "        (\"DOWNEY SAVINGS AND LOAN ASSOCIATION, F.A.\" , \"Downey Mortgage\"),\n",
    "        (\"DITECH FINANCIAL LLC\" , \"Ditech\"),\n",
    "        (\"CITIMORTGAGE, INC.\" , \"Citi\"),\n",
    "        (\"CHICAGO MORTGAGE SOLUTIONS DBA INTERFIRST MORTGAGE COMPANY\" , \"Chicago Mortgage\"),\n",
    "        (\"CHICAGO MORTGAGE SOLUTIONS DBA INTERBANK MORTGAGE COMPANY\" , \"Chicago Mortgage\"),\n",
    "        (\"CHASE HOME FINANCE, LLC\" , \"JP Morgan Chase\"),\n",
    "        (\"CHASE HOME FINANCE FRANKLIN AMERICAN MORTGAGE COMPANY\" , \"JP Morgan Chase\"),\n",
    "        (\"CHASE HOME FINANCE (CIE 1)\" , \"JP Morgan Chase\"),\n",
    "        (\"CHASE HOME FINANCE\" , \"JP Morgan Chase\"),\n",
    "        (\"CASHCALL, INC.\" , \"CashCall\"),\n",
    "        (\"CAPITAL ONE, NATIONAL ASSOCIATION\" , \"Capital One\"),\n",
    "        (\"CALIBER HOME LOANS, INC.\" , \"Caliber Funding\"),\n",
    "        (\"BISHOPS GATE RESIDENTIAL MORTGAGE TRUST\" , \"Bishops Gate Mortgage\"),\n",
    "        (\"BANK OF AMERICA, N.A.\" , \"Bank of America\"),\n",
    "        (\"AMTRUST BANK\" , \"AmTrust\"),\n",
    "        (\"AMERISAVE MORTGAGE CORPORATION\" , \"Amerisave\"),\n",
    "        (\"AMERIHOME MORTGAGE COMPANY, LLC\" , \"AmeriHome Mortgage\"),\n",
    "        (\"ALLY BANK\" , \"Ally Bank\"),\n",
    "        (\"ACADEMY MORTGAGE CORPORATION\" , \"Academy Mortgage\"),\n",
    "        (\"NO CASH-OUT REFINANCE\" , \"OTHER REFINANCE\"),\n",
    "        (\"REFINANCE - NOT SPECIFIED\" , \"OTHER REFINANCE\"),\n",
    "        (\"Other REFINANCE\" , \"OTHER REFINANCE\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Define category (string) column and numeric column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# String columns\n",
    "cate_col_names = [\n",
    "        \"orig_channel\",\n",
    "        \"first_home_buyer\",\n",
    "        \"loan_purpose\",\n",
    "        \"property_type\",\n",
    "        \"occupancy_status\",\n",
    "        \"property_state\",\n",
    "        \"product_type\",\n",
    "        \"relocation_mortgage_indicator\",\n",
    "        \"seller_name\",\n",
    "        \"mod_flag\"\n",
    "]\n",
    "# Numberic columns\n",
    "label_col_name = \"delinquency_12\"\n",
    "numeric_col_names = [\n",
    "        \"orig_interest_rate\",\n",
    "        \"orig_upb\",\n",
    "        \"orig_loan_term\",\n",
    "        \"orig_ltv\",\n",
    "        \"orig_cltv\",\n",
    "        \"num_borrowers\",\n",
    "        \"dti\",\n",
    "        \"borrower_credit_score\",\n",
    "        \"num_units\",\n",
    "        \"zip\",\n",
    "        \"mortgage_insurance_percent\",\n",
    "        \"current_loan_delinquency_status\",\n",
    "        \"current_actual_upb\",\n",
    "        \"interest_rate\",\n",
    "        \"loan_age\",\n",
    "        \"msa\",\n",
    "        \"non_interest_bearing_upb\",\n",
    "        label_col_name\n",
    "]\n",
    "all_col_names = cate_col_names + numeric_col_names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Functions to extract perf and acq columns from raw schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_perf_columns(rawDf):\n",
    "    perfDf = rawDf.select(\n",
    "      col(\"loan_id\"),\n",
    "      date_format(to_date(col(\"monthly_reporting_period\"),\"MMyyyy\"), \"MM/dd/yyyy\").alias(\"monthly_reporting_period\"),\n",
    "      upper(col(\"servicer\")).alias(\"servicer\"),\n",
    "      col(\"interest_rate\"),\n",
    "      col(\"current_actual_upb\"),\n",
    "      col(\"loan_age\"),\n",
    "      col(\"remaining_months_to_legal_maturity\"),\n",
    "      col(\"adj_remaining_months_to_maturity\"),\n",
    "      date_format(to_date(col(\"maturity_date\"),\"MMyyyy\"), \"MM/yyyy\").alias(\"maturity_date\"),\n",
    "      col(\"msa\"),\n",
    "      col(\"current_loan_delinquency_status\"),\n",
    "      col(\"mod_flag\"),\n",
    "      col(\"zero_balance_code\"),\n",
    "      date_format(to_date(col(\"zero_balance_effective_date\"),\"MMyyyy\"), \"MM/yyyy\").alias(\"zero_balance_effective_date\"),\n",
    "      date_format(to_date(col(\"last_paid_installment_date\"),\"MMyyyy\"), \"MM/dd/yyyy\").alias(\"last_paid_installment_date\"),\n",
    "      date_format(to_date(col(\"foreclosed_after\"),\"MMyyyy\"), \"MM/dd/yyyy\").alias(\"foreclosed_after\"),\n",
    "      date_format(to_date(col(\"disposition_date\"),\"MMyyyy\"), \"MM/dd/yyyy\").alias(\"disposition_date\"),\n",
    "      col(\"foreclosure_costs\"),\n",
    "      col(\"prop_preservation_and_repair_costs\"),\n",
    "      col(\"asset_recovery_costs\"),\n",
    "      col(\"misc_holding_expenses\"),\n",
    "      col(\"holding_taxes\"),\n",
    "      col(\"net_sale_proceeds\"),\n",
    "      col(\"credit_enhancement_proceeds\"),\n",
    "      col(\"repurchase_make_whole_proceeds\"),\n",
    "      col(\"other_foreclosure_proceeds\"),\n",
    "      col(\"non_interest_bearing_upb\"),\n",
    "      col(\"principal_forgiveness_upb\"),\n",
    "      col(\"repurchase_make_whole_proceeds_flag\"),\n",
    "      col(\"foreclosure_principal_write_off_amount\"),\n",
    "      col(\"servicing_activity_indicator\"),\n",
    "      col('quarter')\n",
    "    )\n",
    "\n",
    "    return perfDf.select(\"*\").filter(\"current_actual_upb != 0.0\")\n",
    "\n",
    "def extract_acq_columns(rawDf):\n",
    "    acqDf = rawDf.select(\n",
    "      col(\"loan_id\"),\n",
    "      col(\"orig_channel\"),\n",
    "      upper(col(\"seller_name\")).alias(\"seller_name\"),\n",
    "      col(\"orig_interest_rate\"),\n",
    "      col(\"orig_upb\"),\n",
    "      col(\"orig_loan_term\"),\n",
    "      date_format(to_date(col(\"orig_date\"),\"MMyyyy\"), \"MM/yyyy\").alias(\"orig_date\"),\n",
    "      date_format(to_date(col(\"first_pay_date\"),\"MMyyyy\"), \"MM/yyyy\").alias(\"first_pay_date\"),\n",
    "      col(\"orig_ltv\"),\n",
    "      col(\"orig_cltv\"),\n",
    "      col(\"num_borrowers\"),\n",
    "      col(\"dti\"),\n",
    "      col(\"borrower_credit_score\"),\n",
    "      col(\"first_home_buyer\"),\n",
    "      col(\"loan_purpose\"),\n",
    "      col(\"property_type\"),\n",
    "      col(\"num_units\"),\n",
    "      col(\"occupancy_status\"),\n",
    "      col(\"property_state\"),\n",
    "      col(\"zip\"),\n",
    "      col(\"mortgage_insurance_percent\"),\n",
    "      col(\"product_type\"),\n",
    "      col(\"coborrow_credit_score\"),\n",
    "      col(\"mortgage_insurance_type\"),\n",
    "      col(\"relocation_mortgage_indicator\"),\n",
    "      dense_rank().over(Window.partitionBy(\"loan_id\").orderBy(to_date(col(\"monthly_reporting_period\"),\"MMyyyy\"))).alias(\"rank\"),\n",
    "      col('quarter')\n",
    "      )\n",
    "\n",
    "    return acqDf.select(\"*\").filter(col(\"rank\")==1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Define ETL Process\n",
    "\n",
    "Define the function to do the ETL process\n",
    "\n",
    "#### 2.1 Define Functions to Read Raw CSV File\n",
    "\n",
    "* Define function to get quarter from input CSV file name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "def _get_quarter_from_csv_file_name():\n",
    "    return substring_index(substring_index(input_file_name(), '.', 1), '/', -1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Define function to read raw CSV data file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_raw_csv(spark, path):\n",
    "    return spark.read.format('csv') \\\n",
    "            .option('nullValue', '') \\\n",
    "            .option('header', False) \\\n",
    "            .option('delimiter', '|') \\\n",
    "            .schema(_csv_raw_schema) \\\n",
    "            .load(path) \\\n",
    "            .withColumn('quarter', _get_quarter_from_csv_file_name())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.2 Define ETL Process\n",
    "\n",
    "* Define function to parse dates in Performance data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "def _parse_dates(perf):\n",
    "    return perf \\\n",
    "            .withColumn('monthly_reporting_period', to_date(col('monthly_reporting_period'), 'MM/dd/yyyy')) \\\n",
    "            .withColumn('monthly_reporting_period_month', month(col('monthly_reporting_period'))) \\\n",
    "            .withColumn('monthly_reporting_period_year', year(col('monthly_reporting_period'))) \\\n",
    "            .withColumn('monthly_reporting_period_day', dayofmonth(col('monthly_reporting_period'))) \\\n",
    "            .withColumn('last_paid_installment_date', to_date(col('last_paid_installment_date'), 'MM/dd/yyyy')) \\\n",
    "            .withColumn('foreclosed_after', to_date(col('foreclosed_after'), 'MM/dd/yyyy')) \\\n",
    "            .withColumn('disposition_date', to_date(col('disposition_date'), 'MM/dd/yyyy')) \\\n",
    "            .withColumn('maturity_date', to_date(col('maturity_date'), 'MM/yyyy')) \\\n",
    "            .withColumn('zero_balance_effective_date', to_date(col('zero_balance_effective_date'), 'MM/yyyy'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Define function to create deliquency data frame from Performance data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "def _create_perf_deliquency(spark, perf):\n",
    "    aggDF = perf.select(\n",
    "            col(\"quarter\"),\n",
    "            col(\"loan_id\"),\n",
    "            col(\"current_loan_delinquency_status\"),\n",
    "            when(col(\"current_loan_delinquency_status\") >= 1, col(\"monthly_reporting_period\")).alias(\"delinquency_30\"),\n",
    "            when(col(\"current_loan_delinquency_status\") >= 3, col(\"monthly_reporting_period\")).alias(\"delinquency_90\"),\n",
    "            when(col(\"current_loan_delinquency_status\") >= 6, col(\"monthly_reporting_period\")).alias(\"delinquency_180\")) \\\n",
    "            .groupBy(\"quarter\", \"loan_id\") \\\n",
    "            .agg(\n",
    "                max(\"current_loan_delinquency_status\").alias(\"delinquency_12\"),\n",
    "                min(\"delinquency_30\").alias(\"delinquency_30\"),\n",
    "                min(\"delinquency_90\").alias(\"delinquency_90\"),\n",
    "                min(\"delinquency_180\").alias(\"delinquency_180\")) \\\n",
    "            .select(\n",
    "                col(\"quarter\"),\n",
    "                col(\"loan_id\"),\n",
    "                (col(\"delinquency_12\") >= 1).alias(\"ever_30\"),\n",
    "                (col(\"delinquency_12\") >= 3).alias(\"ever_90\"),\n",
    "                (col(\"delinquency_12\") >= 6).alias(\"ever_180\"),\n",
    "                col(\"delinquency_30\"),\n",
    "                col(\"delinquency_90\"),\n",
    "                col(\"delinquency_180\"))\n",
    "    joinedDf = perf \\\n",
    "            .withColumnRenamed(\"monthly_reporting_period\", \"timestamp\") \\\n",
    "            .withColumnRenamed(\"monthly_reporting_period_month\", \"timestamp_month\") \\\n",
    "            .withColumnRenamed(\"monthly_reporting_period_year\", \"timestamp_year\") \\\n",
    "            .withColumnRenamed(\"current_loan_delinquency_status\", \"delinquency_12\") \\\n",
    "            .withColumnRenamed(\"current_actual_upb\", \"upb_12\") \\\n",
    "            .select(\"quarter\", \"loan_id\", \"timestamp\", \"delinquency_12\", \"upb_12\", \"timestamp_month\", \"timestamp_year\") \\\n",
    "            .join(aggDF, [\"loan_id\", \"quarter\"], \"left_outer\")\n",
    "\n",
    "    # calculate the 12 month delinquency and upb values\n",
    "    months = 12\n",
    "    monthArray = [lit(x) for x in range(0, 12)]\n",
    "    # explode on a small amount of data is actually slightly more efficient than a cross join\n",
    "    testDf = joinedDf \\\n",
    "            .withColumn(\"month_y\", explode(array(monthArray))) \\\n",
    "            .select(\n",
    "                    col(\"quarter\"),\n",
    "                    floor(((col(\"timestamp_year\") * 12 + col(\"timestamp_month\")) - 24000) / months).alias(\"josh_mody\"),\n",
    "                    floor(((col(\"timestamp_year\") * 12 + col(\"timestamp_month\")) - 24000 - col(\"month_y\")) / months).alias(\"josh_mody_n\"),\n",
    "                    col(\"ever_30\"),\n",
    "                    col(\"ever_90\"),\n",
    "                    col(\"ever_180\"),\n",
    "                    col(\"delinquency_30\"),\n",
    "                    col(\"delinquency_90\"),\n",
    "                    col(\"delinquency_180\"),\n",
    "                    col(\"loan_id\"),\n",
    "                    col(\"month_y\"),\n",
    "                    col(\"delinquency_12\"),\n",
    "                    col(\"upb_12\")) \\\n",
    "            .groupBy(\"quarter\", \"loan_id\", \"josh_mody_n\", \"ever_30\", \"ever_90\", \"ever_180\", \"delinquency_30\", \"delinquency_90\", \"delinquency_180\", \"month_y\") \\\n",
    "            .agg(max(\"delinquency_12\").alias(\"delinquency_12\"), min(\"upb_12\").alias(\"upb_12\")) \\\n",
    "            .withColumn(\"timestamp_year\", floor((lit(24000) + (col(\"josh_mody_n\") * lit(months)) + (col(\"month_y\") - 1)) / lit(12))) \\\n",
    "            .selectExpr('*', 'pmod(24000 + (josh_mody_n * {}) + month_y, 12) as timestamp_month_tmp'.format(months)) \\\n",
    "            .withColumn(\"timestamp_month\", when(col(\"timestamp_month_tmp\") == lit(0), lit(12)).otherwise(col(\"timestamp_month_tmp\"))) \\\n",
    "            .withColumn(\"delinquency_12\", ((col(\"delinquency_12\") > 3).cast(\"int\") + (col(\"upb_12\") == 0).cast(\"int\")).alias(\"delinquency_12\")) \\\n",
    "            .drop(\"timestamp_month_tmp\", \"josh_mody_n\", \"month_y\")\n",
    "\n",
    "    return perf.withColumnRenamed(\"monthly_reporting_period_month\", \"timestamp_month\") \\\n",
    "            .withColumnRenamed(\"monthly_reporting_period_year\", \"timestamp_year\") \\\n",
    "            .join(testDf, [\"quarter\", \"loan_id\", \"timestamp_year\", \"timestamp_month\"], \"left\") \\\n",
    "            .drop(\"timestamp_year\", \"timestamp_month\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Define function to create acquisition data frame from Acquisition data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "def _create_acquisition(spark, acq):\n",
    "    nameMapping = spark.createDataFrame(_name_mapping, [\"from_seller_name\", \"to_seller_name\"])\n",
    "    return acq.join(nameMapping, col(\"seller_name\") == col(\"from_seller_name\"), \"left\") \\\n",
    "      .drop(\"from_seller_name\") \\\n",
    "      .withColumn(\"old_name\", col(\"seller_name\")) \\\n",
    "      .withColumn(\"seller_name\", coalesce(col(\"to_seller_name\"), col(\"seller_name\"))) \\\n",
    "      .drop(\"to_seller_name\") \\\n",
    "      .withColumn(\"orig_date\", to_date(col(\"orig_date\"), \"MM/yyyy\")) \\\n",
    "      .withColumn(\"first_pay_date\", to_date(col(\"first_pay_date\"), \"MM/yyyy\")) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.3 Define Casting Process\n",
    "This part is casting String column to Numbric. \n",
    "Example:\n",
    "```\n",
    "col_1\n",
    " \"a\"\n",
    " \"b\"\n",
    " \"c\"\n",
    " \"a\"\n",
    "# After String ====> Numberic\n",
    "col_1\n",
    " 0\n",
    " 1\n",
    " 2\n",
    " 0\n",
    "```  \n",
    "<br>\n",
    "\n",
    "* Define function to get column dictionary\n",
    "\n",
    "    Example\n",
    "    ```\n",
    "    col1 = [row(data=\"a\",id=0), row(data=\"b\",id=1)]\n",
    "    ```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "def _gen_dictionary(etl_df, col_names):\n",
    "    cnt_table = etl_df.select(posexplode(array([col(i) for i in col_names])))\\\n",
    "                    .withColumnRenamed(\"pos\", \"column_id\")\\\n",
    "                    .withColumnRenamed(\"col\", \"data\")\\\n",
    "                    .filter(\"data is not null\")\\\n",
    "                    .groupBy(\"column_id\", \"data\")\\\n",
    "                    .count()\n",
    "    windowed = Window.partitionBy(\"column_id\").orderBy(desc(\"count\"))\n",
    "    return cnt_table.withColumn(\"id\", row_number().over(windowed)).drop(\"count\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Define function to convert string columns to numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "def _cast_string_columns_to_numeric(spark, input_df):\n",
    "    cached_dict_df = _gen_dictionary(input_df, cate_col_names).cache()\n",
    "    output_df = input_df\n",
    "    #  Generate the final table with all columns being numeric.\n",
    "    for col_pos, col_name in enumerate(cate_col_names):\n",
    "        col_dict_df = cached_dict_df.filter(col(\"column_id\") == col_pos)\\\n",
    "                                    .drop(\"column_id\")\\\n",
    "                                    .withColumnRenamed(\"data\", col_name)\n",
    "        \n",
    "        output_df = output_df.join(broadcast(col_dict_df), col_name, \"left\")\\\n",
    "                        .drop(col_name)\\\n",
    "                        .withColumnRenamed(\"id\", col_name)\n",
    "    return output_df        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.4 Define Main Function\n",
    "In this function:\n",
    "1. Parse date in Performance data by calling _parse_dates (parsed_perf)\n",
    "2. Create deliqency dataframe(perf_deliqency) form Performance data by calling _create_perf_deliquency\n",
    "3. Create cleaned acquisition dataframe(cleaned_acq) from Acquisition data by calling _create_acquisition\n",
    "4. Join deliqency dataframe(perf_deliqency) and cleaned acquisition dataframe(cleaned_acq), get clean_df\n",
    "5. Cast String column to Numbric in clean_df by calling _cast_string_columns_to_numeric, get casted_clean_df\n",
    "6. Return casted_clean_df as final result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "def run_mortgage(spark, perf, acq):\n",
    "    parsed_perf = _parse_dates(perf)\n",
    "    perf_deliqency = _create_perf_deliquency(spark, parsed_perf)\n",
    "    cleaned_acq = _create_acquisition(spark, acq)\n",
    "    clean_df = perf_deliqency.join(cleaned_acq, [\"loan_id\", \"quarter\"], \"inner\").drop(\"quarter\")\n",
    "    casted_clean_df = _cast_string_columns_to_numeric(spark, clean_df)\\\n",
    "                    .select(all_col_names)\\\n",
    "                    .withColumn(label_col_name, when(col(label_col_name) > 0, 1).otherwise(0))\\\n",
    "                    .fillna(float(0))\n",
    "    return casted_clean_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Script Settings\n",
    "\n",
    "### 1. File Path Settings\n",
    "* Define input file path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# You need to update them to your real paths!\n",
    "dataRoot = os.getenv(\"DATA_ROOT\", \"/data\")\n",
    "orig_raw_path = dataRoot + '/mortgage/input/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Define output folder path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_path = dataRoot + '/mortgage/output/data/'\n",
    "output_csv2parquet = dataRoot + '/mortgage/output/csv2parquet/'\n",
    "output_path_train = dataRoot + '/mortgage/output/train/'\n",
    "output_path_eval = dataRoot + '/mortgage/output/eval/'\n",
    "save_train_eval_dataset = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Common Spark Settings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark.conf.set('spark.rapids.sql.explain', 'ALL')\n",
    "spark.conf.set('spark.rapids.sql.batchSizeBytes', '512M')\n",
    "spark.conf.set('spark.rapids.sql.reader.batchSizeBytes', '768M')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run Part"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read Raw File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rawDf = read_raw_csv(spark, orig_raw_path)\n",
    "rawDf.write.parquet(output_csv2parquet, mode='overwrite')\n",
    "rawDf = spark.read.parquet(output_csv2parquet)\n",
    "\n",
    "acq = extract_acq_columns(rawDf)\n",
    "perf = extract_perf_columns(rawDf)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run ETL\n",
    "#### 1. Add additional Spark settings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "# GPU run, set to true\n",
    "spark.conf.set('spark.rapids.sql.enabled', 'true')\n",
    "# CPU run, set to false\n",
    "# spark.conf.set('spark.rapids.sql.enabled', 'false')\n",
    "spark.conf.set('spark.sql.files.maxPartitionBytes', '1G')\n",
    "# use GPU to read CSV\n",
    "spark.conf.set(\"spark.rapids.sql.csv.read.double.enabled\", \"true\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.Read Parquet File and Run ETL Process, Save the Result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "== Physical Plan ==\n",
      "GpuColumnarToRow false\n",
      "+- GpuProject [gpucoalesce(orig_channel#3146, 0) AS orig_channel#5143, gpucoalesce(first_home_buyer#3351, 0) AS first_home_buyer#5144, gpucoalesce(loan_purpose#3556, 0) AS loan_purpose#5145, gpucoalesce(property_type#3761, 0) AS property_type#5146, gpucoalesce(occupancy_status#3966, 0) AS occupancy_status#5147, gpucoalesce(property_state#4171, 0) AS property_state#5148, gpucoalesce(product_type#4376, 0) AS product_type#5149, gpucoalesce(relocation_mortgage_indicator#4581, 0) AS relocation_mortgage_indicator#5150, gpucoalesce(seller_name#4786, 0) AS seller_name#5151, gpucoalesce(id#2956, 0) AS mod_flag#5152, gpucoalesce(gpunanvl(orig_interest_rate#1606, null), 0.0) AS orig_interest_rate#5153, gpucoalesce(orig_upb#1607, 0) AS orig_upb#5154, gpucoalesce(orig_loan_term#1608, 0) AS orig_loan_term#5155, gpucoalesce(gpunanvl(orig_ltv#1611, null), 0.0) AS orig_ltv#5156, gpucoalesce(gpunanvl(orig_cltv#1612, null), 0.0) AS orig_cltv#5157, gpucoalesce(gpunanvl(num_borrowers#1613, null), 0.0) AS num_borrowers#5158, gpucoalesce(gpunanvl(dti#1614, null), 0.0) AS dti#5159, gpucoalesce(gpunanvl(borrower_credit_score#1615, null), 0.0) AS borrower_credit_score#5160, gpucoalesce(num_units#1619, 0) AS num_units#5161, gpucoalesce(zip#1622, 0) AS zip#5162, gpucoalesce(gpunanvl(mortgage_insurance_percent#1623, null), 0.0) AS mortgage_insurance_percent#5163, gpucoalesce(current_loan_delinquency_status#1549, 0) AS current_loan_delinquency_status#5164, gpucoalesce(gpunanvl(current_actual_upb#1543, null), 0.0) AS current_actual_upb#5165, gpucoalesce(gpunanvl(interest_rate#1542, null), 0.0) AS interest_rate#5166, ... 4 more fields]\n",
      "   +- GpuBroadcastHashJoin [mod_flag#1550], [mod_flag#4855], LeftOuter, GpuBuildRight\n",
      "      :- GpuProject [interest_rate#1542, current_actual_upb#1543, loan_age#1544, msa#1548, current_loan_delinquency_status#1549, mod_flag#1550, non_interest_bearing_upb#1565, delinquency_12#2353, orig_interest_rate#1606, orig_upb#1607, orig_loan_term#1608, orig_ltv#1611, orig_cltv#1612, num_borrowers#1613, dti#1614, borrower_credit_score#1615, num_units#1619, zip#1622, mortgage_insurance_percent#1623, orig_channel#3146, first_home_buyer#3351, loan_purpose#3556, property_type#3761, occupancy_status#3966, ... 4 more fields]\n",
      "      :  +- GpuBroadcastHashJoin [seller_name#2689], [seller_name#4650], LeftOuter, GpuBuildRight\n",
      "      :     :- GpuProject [interest_rate#1542, current_actual_upb#1543, loan_age#1544, msa#1548, current_loan_delinquency_status#1549, mod_flag#1550, non_interest_bearing_upb#1565, delinquency_12#2353, seller_name#2689, orig_interest_rate#1606, orig_upb#1607, orig_loan_term#1608, orig_ltv#1611, orig_cltv#1612, num_borrowers#1613, dti#1614, borrower_credit_score#1615, num_units#1619, zip#1622, mortgage_insurance_percent#1623, orig_channel#3146, first_home_buyer#3351, loan_purpose#3556, property_type#3761, ... 4 more fields]\n",
      "      :     :  +- GpuBroadcastHashJoin [relocation_mortgage_indicator#1627], [relocation_mortgage_indicator#4445], LeftOuter, GpuBuildRight\n",
      "      :     :     :- GpuProject [interest_rate#1542, current_actual_upb#1543, loan_age#1544, msa#1548, current_loan_delinquency_status#1549, mod_flag#1550, non_interest_bearing_upb#1565, delinquency_12#2353, seller_name#2689, orig_interest_rate#1606, orig_upb#1607, orig_loan_term#1608, orig_ltv#1611, orig_cltv#1612, num_borrowers#1613, dti#1614, borrower_credit_score#1615, num_units#1619, zip#1622, mortgage_insurance_percent#1623, relocation_mortgage_indicator#1627, orig_channel#3146, first_home_buyer#3351, loan_purpose#3556, ... 4 more fields]\n",
      "      :     :     :  +- GpuBroadcastHashJoin [product_type#1624], [product_type#4240], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :- GpuProject [interest_rate#1542, current_actual_upb#1543, loan_age#1544, msa#1548, current_loan_delinquency_status#1549, mod_flag#1550, non_interest_bearing_upb#1565, delinquency_12#2353, seller_name#2689, orig_interest_rate#1606, orig_upb#1607, orig_loan_term#1608, orig_ltv#1611, orig_cltv#1612, num_borrowers#1613, dti#1614, borrower_credit_score#1615, num_units#1619, zip#1622, mortgage_insurance_percent#1623, product_type#1624, relocation_mortgage_indicator#1627, orig_channel#3146, first_home_buyer#3351, ... 4 more fields]\n",
      "      :     :     :     :  +- GpuBroadcastHashJoin [property_state#1621], [property_state#4035], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :     :- GpuProject [interest_rate#1542, current_actual_upb#1543, loan_age#1544, msa#1548, current_loan_delinquency_status#1549, mod_flag#1550, non_interest_bearing_upb#1565, delinquency_12#2353, seller_name#2689, orig_interest_rate#1606, orig_upb#1607, orig_loan_term#1608, orig_ltv#1611, orig_cltv#1612, num_borrowers#1613, dti#1614, borrower_credit_score#1615, num_units#1619, property_state#1621, zip#1622, mortgage_insurance_percent#1623, product_type#1624, relocation_mortgage_indicator#1627, orig_channel#3146, ... 4 more fields]\n",
      "      :     :     :     :     :  +- GpuBroadcastHashJoin [occupancy_status#1620], [occupancy_status#3830], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :     :     :- GpuProject [interest_rate#1542, current_actual_upb#1543, loan_age#1544, msa#1548, current_loan_delinquency_status#1549, mod_flag#1550, non_interest_bearing_upb#1565, delinquency_12#2353, seller_name#2689, orig_interest_rate#1606, orig_upb#1607, orig_loan_term#1608, orig_ltv#1611, orig_cltv#1612, num_borrowers#1613, dti#1614, borrower_credit_score#1615, num_units#1619, occupancy_status#1620, property_state#1621, zip#1622, mortgage_insurance_percent#1623, product_type#1624, relocation_mortgage_indicator#1627, ... 4 more fields]\n",
      "      :     :     :     :     :     :  +- GpuBroadcastHashJoin [property_type#1618], [property_type#3625], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :     :     :     :- GpuProject [interest_rate#1542, current_actual_upb#1543, loan_age#1544, msa#1548, current_loan_delinquency_status#1549, mod_flag#1550, non_interest_bearing_upb#1565, delinquency_12#2353, seller_name#2689, orig_interest_rate#1606, orig_upb#1607, orig_loan_term#1608, orig_ltv#1611, orig_cltv#1612, num_borrowers#1613, dti#1614, borrower_credit_score#1615, property_type#1618, num_units#1619, occupancy_status#1620, property_state#1621, zip#1622, mortgage_insurance_percent#1623, product_type#1624, ... 4 more fields]\n",
      "      :     :     :     :     :     :     :  +- GpuBroadcastHashJoin [loan_purpose#1617], [loan_purpose#3420], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :     :     :     :     :- GpuProject [interest_rate#1542, current_actual_upb#1543, loan_age#1544, msa#1548, current_loan_delinquency_status#1549, mod_flag#1550, non_interest_bearing_upb#1565, delinquency_12#2353, seller_name#2689, orig_interest_rate#1606, orig_upb#1607, orig_loan_term#1608, orig_ltv#1611, orig_cltv#1612, num_borrowers#1613, dti#1614, borrower_credit_score#1615, loan_purpose#1617, property_type#1618, num_units#1619, occupancy_status#1620, property_state#1621, zip#1622, mortgage_insurance_percent#1623, ... 4 more fields]\n",
      "      :     :     :     :     :     :     :     :  +- GpuBroadcastHashJoin [first_home_buyer#1616], [first_home_buyer#3215], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :     :     :     :     :     :- GpuProject [interest_rate#1542, current_actual_upb#1543, loan_age#1544, msa#1548, current_loan_delinquency_status#1549, mod_flag#1550, non_interest_bearing_upb#1565, delinquency_12#2353, seller_name#2689, orig_interest_rate#1606, orig_upb#1607, orig_loan_term#1608, orig_ltv#1611, orig_cltv#1612, num_borrowers#1613, dti#1614, borrower_credit_score#1615, first_home_buyer#1616, loan_purpose#1617, property_type#1618, num_units#1619, occupancy_status#1620, property_state#1621, zip#1622, ... 4 more fields]\n",
      "      :     :     :     :     :     :     :     :     :  +- GpuBroadcastHashJoin [orig_channel#1604], [orig_channel#3010], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :     :     :     :     :     :     :- GpuProject [interest_rate#1542, current_actual_upb#1543, loan_age#1544, msa#1548, current_loan_delinquency_status#1549, mod_flag#1550, non_interest_bearing_upb#1565, delinquency_12#2353, orig_channel#1604, seller_name#2689, orig_interest_rate#1606, orig_upb#1607, orig_loan_term#1608, orig_ltv#1611, orig_cltv#1612, num_borrowers#1613, dti#1614, borrower_credit_score#1615, first_home_buyer#1616, loan_purpose#1617, property_type#1618, num_units#1619, occupancy_status#1620, property_state#1621, ... 4 more fields]\n",
      "      :     :     :     :     :     :     :     :     :     :  +- GpuShuffledHashJoin [loan_id#1539L, quarter#1570], [loan_id#1603L, quarter#1629], Inner, GpuBuildRight, false\n",
      "      :     :     :     :     :     :     :     :     :     :     :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :     :     :     :  +- GpuColumnarExchange gpuhashpartitioning(loan_id#1539L, quarter#1570, 192), ENSURE_REQUIREMENTS, [id=#3885]\n",
      "      :     :     :     :     :     :     :     :     :     :     :     +- GpuProject [quarter#1570, loan_id#1539L, interest_rate#1542, current_actual_upb#1543, loan_age#1544, msa#1548, current_loan_delinquency_status#1549, mod_flag#1550, non_interest_bearing_upb#1565, delinquency_12#2353]\n",
      "      :     :     :     :     :     :     :     :     :     :     :        +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :     :     :           +- SortMergeJoin [quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint)], [quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L], LeftOuter\n",
      "      :     :     :     :     :     :     :     :     :     :     :              :- *(2) Sort [quarter#1570 ASC NULLS FIRST, loan_id#1539L ASC NULLS FIRST, cast(timestamp_year#2417 as bigint) ASC NULLS FIRST, cast(timestamp_month#2381 as bigint) ASC NULLS FIRST], false, 0\n",
      "      :     :     :     :     :     :     :     :     :     :     :              :  +- Exchange hashpartitioning(quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint), 192), ENSURE_REQUIREMENTS, [id=#3847]\n",
      "      :     :     :     :     :     :     :     :     :     :     :              :     +- *(1) Project [loan_id#1539L, interest_rate#1542, current_actual_upb#1543, loan_age#1544, msa#1548, current_loan_delinquency_status#1549, mod_flag#1550, non_interest_bearing_upb#1565, quarter#1570, month(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2381, year(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2417]\n",
      "      :     :     :     :     :     :     :     :     :     :     :              :        +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :     :     :     :              :           +- GpuFilter (gpuisnotnull(loan_id#1539L) AND gpuisnotnull(quarter#1570)), true\n",
      "      :     :     :     :     :     :     :     :     :     :     :              :              +- GpuFileGpuScan parquet [loan_id#1539L,monthly_reporting_period#1540,interest_rate#1542,current_actual_upb#1543,loan_age#1544,msa#1548,current_loan_delinquency_status#1549,mod_flag#1550,non_interest_bearing_upb#1565,quarter#1570] Batched: true, DataFilters: [isnotnull(loan_id#1539L), isnotnull(quarter#1570)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,interest_rate:double,current_actual_upb:dou...\n",
      "      :     :     :     :     :     :     :     :     :     :     :              +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :     :     :     :                 +- GpuSort [quarter#2484 ASC NULLS FIRST, loan_id#2453L ASC NULLS FIRST, timestamp_year#2307L ASC NULLS FIRST, timestamp_month#2336L ASC NULLS FIRST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :     :     :     :     :     :     :     :     :                    +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :     :     :     :                       +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L, 192), ENSURE_REQUIREMENTS, [id=#3878]\n",
      "      :     :     :     :     :     :     :     :     :     :     :                          +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :     :     :                             +- *(6) HashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[max(delinquency_12#2123), min(upb_12#2159)])\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                +- Exchange hashpartitioning(quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248, 192), ENSURE_REQUIREMENTS, [id=#3873]\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                   +- *(5) HashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[partial_max(delinquency_12#2123), partial_min(upb_12#2159)])\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                      +- *(5) Project [quarter#2484, FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) AS josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, loan_id#2453L, month_y#2248, delinquency_12#2123, upb_12#2159]\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                         +- *(5) Filter (isnotnull(FLOOR((cast(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast((month_y#2248 - 1) as bigint)) as double) / 12.0))) AND isnotnull(CASE WHEN (pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) = 0) THEN 12 ELSE pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) END))\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                            +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                               +- GpuGenerate gpuexplode([0,1,2,3,4,5,6,7,8,9,10,11]), [loan_id#2453L, quarter#2484, delinquency_12#2123, upb_12#2159, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997], false, [month_y#2248]\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                  +- GpuProject [loan_id#2453L, quarter#2484, delinquency_12#2123, upb_12#2159, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997]\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                     +- GpuBroadcastHashJoin [loan_id#2453L, quarter#2484], [loan_id#2202L, quarter#2233], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                        :- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                        :  +- *(3) Project [quarter#2484, loan_id#2453L, current_loan_delinquency_status#2463 AS delinquency_12#2123, current_actual_upb#2457 AS upb_12#2159, month(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2051, year(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2087]\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                        :     +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                        :        +- GpuFilter (gpuisnotnull(quarter#2484) AND gpuisnotnull(loan_id#2453L)), true\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                        :           +- GpuFileGpuScan parquet [loan_id#2453L,monthly_reporting_period#2454,current_actual_upb#2457,current_loan_delinquency_status#2463,quarter#2484] Batched: true, DataFilters: [isnotnull(quarter#2484), isnotnull(loan_id#2453L)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(quarter), IsNotNull(loan_id)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,current_actual_upb:double,current_loan_deli...\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                        +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[1, bigint, true], input[0, string, true]),false), [id=#3863]\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                           +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[gpumax(current_loan_delinquency_status#2212), gpumin(delinquency_30#1975), gpumin(delinquency_90#1976), gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                              +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                                 +- GpuColumnarExchange gpuhashpartitioning(quarter#2233, loan_id#2202L, 192), ENSURE_REQUIREMENTS, [id=#3860]\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                                    +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[partial_gpumax(current_loan_delinquency_status#2212), partial_gpumin(delinquency_30#1975), partial_gpumin(delinquency_90#1976), partial_gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                                       +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                                          +- *(4) Project [quarter#2233, loan_id#2202L, current_loan_delinquency_status#2212, CASE WHEN (current_loan_delinquency_status#2212 >= 1) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_30#1975, CASE WHEN (current_loan_delinquency_status#2212 >= 3) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_90#1976, CASE WHEN (current_loan_delinquency_status#2212 >= 6) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_180#1977]\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                                             +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                                                +- GpuFilter (gpuisnotnull(loan_id#2202L) AND gpuisnotnull(quarter#2233)), true\n",
      "      :     :     :     :     :     :     :     :     :     :     :                                                                                   +- GpuFileGpuScan parquet [loan_id#2202L,monthly_reporting_period#2203,current_loan_delinquency_status#2212,quarter#2233] Batched: true, DataFilters: [isnotnull(loan_id#2202L), isnotnull(quarter#2233)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,current_loan_delinquency_status:int,quarter...\n",
      "      :     :     :     :     :     :     :     :     :     :     +- GpuColumnarExchange gpuhashpartitioning(loan_id#1603L, quarter#1629, 192), ENSURE_REQUIREMENTS, [id=#3894]\n",
      "      :     :     :     :     :     :     :     :     :     :        +- GpuProject [loan_id#1603L, orig_channel#1604, gpucoalesce(to_seller_name#2570, seller_name#1605) AS seller_name#2689, orig_interest_rate#1606, orig_upb#1607, orig_loan_term#1608, orig_ltv#1611, orig_cltv#1612, num_borrowers#1613, dti#1614, borrower_credit_score#1615, first_home_buyer#1616, loan_purpose#1617, property_type#1618, num_units#1619, occupancy_status#1620, property_state#1621, zip#1622, mortgage_insurance_percent#1623, product_type#1624, relocation_mortgage_indicator#1627, quarter#1629]\n",
      "      :     :     :     :     :     :     :     :     :     :           +- GpuShuffledHashJoin [seller_name#1605], [from_seller_name#2569], LeftOuter, GpuBuildRight, false\n",
      "      :     :     :     :     :     :     :     :     :     :              :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :     :     :              :  +- GpuColumnarExchange gpuhashpartitioning(seller_name#1605, 192), ENSURE_REQUIREMENTS, [id=#3523]\n",
      "      :     :     :     :     :     :     :     :     :     :              :     +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :     :              :        +- GpuFilter (gpuisnotnull(loan_id#1603L) AND gpuisnotnull(quarter#1629)), true\n",
      "      :     :     :     :     :     :     :     :     :     :              :           +- GpuFileGpuScan parquet [loan_id#1603L,orig_channel#1604,seller_name#1605,orig_interest_rate#1606,orig_upb#1607,orig_loan_term#1608,orig_ltv#1611,orig_cltv#1612,num_borrowers#1613,dti#1614,borrower_credit_score#1615,first_home_buyer#1616,loan_purpose#1617,property_type#1618,num_units#1619,occupancy_status#1620,property_state#1621,zip#1622,mortgage_insurance_percent#1623,product_type#1624,relocation_mortgage_indicator#1627,quarter#1629] Batched: true, DataFilters: [isnotnull(loan_id#1603L), isnotnull(quarter#1629)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/acq], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,orig_channel:string,seller_name:string,orig_interest_rate:double,orig_upb:i...\n",
      "      :     :     :     :     :     :     :     :     :     :              +- GpuColumnarExchange gpuhashpartitioning(from_seller_name#2569, 192), ENSURE_REQUIREMENTS, [id=#3891]\n",
      "      :     :     :     :     :     :     :     :     :     :                 +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :     :                    +- GpuFilter gpuisnotnull(from_seller_name#2569), true\n",
      "      :     :     :     :     :     :     :     :     :     :                       +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :     :                          +- *(7) Scan ExistingRDD[from_seller_name#2569,to_seller_name#2570]\n",
      "      :     :     :     :     :     :     :     :     :     +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true]),false), [id=#3694]\n",
      "      :     :     :     :     :     :     :     :     :        +- GpuProject [data#2945 AS orig_channel#3010, id#2956]\n",
      "      :     :     :     :     :     :     :     :     :           +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :              +- GpuFilter ((column_id#2942 = 0) AND gpuisnotnull(data#2945)), true\n",
      "      :     :     :     :     :     :     :     :     :                 +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :                    +- InMemoryTableScan [column_id#2942, data#2945, id#2956], [(column_id#2942 = 0), isnotnull(data#2945)]\n",
      "      :     :     :     :     :     :     :     :     :                          +- InMemoryRelation [column_id#2942, data#2945, id#2956], StorageLevel(disk, memory, deserialized, 1 replicas)\n",
      "      :     :     :     :     :     :     :     :     :                                +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :     :                                   +- GpuProject [column_id#2942, data#2945, id#2956]\n",
      "      :     :     :     :     :     :     :     :     :                                      +- GpuRunningWindow [column_id#2942, data#2945, count#2951L, gpurownumber$() gpuwindowspecdefinition(column_id#2942, count#2951L DESC NULLS LAST, gpuspecifiedwindowframe(RowFrame, gpuspecialframeboundary(unboundedpreceding$()), gpuspecialframeboundary(currentrow$()))) AS id#2956], [column_id#2942], [count#2951L DESC NULLS LAST]\n",
      "      :     :     :     :     :     :     :     :     :                                         +- GpuSort [column_id#2942 ASC NULLS FIRST, count#2951L DESC NULLS LAST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :     :     :     :     :     :     :                                            +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :     :                                               +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, 192), ENSURE_REQUIREMENTS, [id=#1141]\n",
      "      :     :     :     :     :     :     :     :     :                                                  +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :     :     :     :     :     :     :                                                     +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :     :                                                        +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, data#2945, 192), ENSURE_REQUIREMENTS, [id=#1138]\n",
      "      :     :     :     :     :     :     :     :     :                                                           +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[partial_gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :     :     :     :     :     :     :                                                              +- GpuProject [pos#2938 AS column_id#2942, col#2939 AS data#2945]\n",
      "      :     :     :     :     :     :     :     :     :                                                                 +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :                                                                    +- GpuFilter gpuisnotnull(col#2939), true\n",
      "      :     :     :     :     :     :     :     :     :                                                                       +- GpuGenerate gpuposexplode(array(orig_channel#1604, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, seller_name#2689, mod_flag#1550)), false, [pos#2938, col#2939]\n",
      "      :     :     :     :     :     :     :     :     :                                                                          +- GpuProject [mod_flag#1550, orig_channel#1604, seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627]\n",
      "      :     :     :     :     :     :     :     :     :                                                                             +- GpuShuffledHashJoin [loan_id#1539L, quarter#1570], [loan_id#1603L, quarter#1629], Inner, GpuBuildRight, false\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :  +- GpuColumnarExchange gpuhashpartitioning(loan_id#1539L, quarter#1570, 192), ENSURE_REQUIREMENTS, [id=#1121]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :     +- GpuProject [quarter#1570, loan_id#1539L, mod_flag#1550]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :        +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :           +- SortMergeJoin [quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint)], [quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L], LeftOuter\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :              :- *(2) Sort [quarter#1570 ASC NULLS FIRST, loan_id#1539L ASC NULLS FIRST, cast(timestamp_year#2417 as bigint) ASC NULLS FIRST, cast(timestamp_month#2381 as bigint) ASC NULLS FIRST], false, 0\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :              :  +- Exchange hashpartitioning(quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint), 192), ENSURE_REQUIREMENTS, [id=#1080]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :              :     +- *(1) Project [loan_id#1539L, mod_flag#1550, quarter#1570, month(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2381, year(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2417]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :              :        +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :              :           +- GpuFilter (gpuisnotnull(loan_id#1539L) AND gpuisnotnull(quarter#1570)), true\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :              :              +- GpuFileGpuScan parquet [loan_id#1539L,monthly_reporting_period#1540,mod_flag#1550,quarter#1570] Batched: true, DataFilters: [isnotnull(loan_id#1539L), isnotnull(quarter#1570)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,mod_flag:string,quarter:string>\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :              +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                 +- GpuSort [quarter#2484 ASC NULLS FIRST, loan_id#2453L ASC NULLS FIRST, timestamp_year#2307L ASC NULLS FIRST, timestamp_month#2336L ASC NULLS FIRST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                    +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                       +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L, 192), ENSURE_REQUIREMENTS, [id=#1114]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                          +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                             +- *(6) HashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[])\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                   +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                      +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248, 192), ENSURE_REQUIREMENTS, [id=#1107]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                         +- GpuHashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[]), filters=ArrayBuffer())\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                            +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                               +- *(5) Project [quarter#2484, FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) AS josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, loan_id#2453L, month_y#2248]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                  +- *(5) Filter (isnotnull(FLOOR((cast(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast((month_y#2248 - 1) as bigint)) as double) / 12.0))) AND isnotnull(CASE WHEN (pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) = 0) THEN 12 ELSE pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) END))\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                     +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                        +- GpuGenerate gpuexplode([0,1,2,3,4,5,6,7,8,9,10,11]), [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997], false, [month_y#2248]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                           +- GpuProject [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                              +- GpuBroadcastHashJoin [loan_id#2453L, quarter#2484], [loan_id#2202L, quarter#2233], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                 :- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                 :  +- *(3) Project [quarter#2484, loan_id#2453L, month(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2051, year(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2087]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                 :     +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                 :        +- GpuFilter (gpuisnotnull(quarter#2484) AND gpuisnotnull(loan_id#2453L)), true\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                 :           +- GpuFileGpuScan parquet [loan_id#2453L,monthly_reporting_period#2454,quarter#2484] Batched: true, DataFilters: [isnotnull(quarter#2484), isnotnull(loan_id#2453L)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(quarter), IsNotNull(loan_id)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,quarter:string>\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                 +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[1, bigint, true], input[0, string, true]),false), [id=#1096]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                    +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[gpumax(current_loan_delinquency_status#2212), gpumin(delinquency_30#1975), gpumin(delinquency_90#1976), gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                       +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                          +- GpuColumnarExchange gpuhashpartitioning(quarter#2233, loan_id#2202L, 192), ENSURE_REQUIREMENTS, [id=#1093]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                             +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[partial_gpumax(current_loan_delinquency_status#2212), partial_gpumin(delinquency_30#1975), partial_gpumin(delinquency_90#1976), partial_gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                                +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                                   +- *(4) Project [quarter#2233, loan_id#2202L, current_loan_delinquency_status#2212, CASE WHEN (current_loan_delinquency_status#2212 >= 1) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_30#1975, CASE WHEN (current_loan_delinquency_status#2212 >= 3) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_90#1976, CASE WHEN (current_loan_delinquency_status#2212 >= 6) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_180#1977]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                                      +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                                         +- GpuFilter (gpuisnotnull(loan_id#2202L) AND gpuisnotnull(quarter#2233)), true\n",
      "      :     :     :     :     :     :     :     :     :                                                                                :                                                                                            +- GpuFileGpuScan parquet [loan_id#2202L,monthly_reporting_period#2203,current_loan_delinquency_status#2212,quarter#2233] Batched: true, DataFilters: [isnotnull(loan_id#2202L), isnotnull(quarter#2233)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,current_loan_delinquency_status:int,quarter...\n",
      "      :     :     :     :     :     :     :     :     :                                                                                +- GpuColumnarExchange gpuhashpartitioning(loan_id#1603L, quarter#1629, 192), ENSURE_REQUIREMENTS, [id=#1130]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                   +- GpuProject [loan_id#1603L, orig_channel#1604, gpucoalesce(to_seller_name#2570, seller_name#1605) AS seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, quarter#1629]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                      +- GpuShuffledHashJoin [seller_name#1605], [from_seller_name#2569], LeftOuter, GpuBuildRight, false\n",
      "      :     :     :     :     :     :     :     :     :                                                                                         :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :     :                                                                                         :  +- GpuColumnarExchange gpuhashpartitioning(seller_name#1605, 192), ENSURE_REQUIREMENTS, [id=#862]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                         :     +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :                                                                                         :        +- GpuFilter (gpuisnotnull(loan_id#1603L) AND gpuisnotnull(quarter#1629)), true\n",
      "      :     :     :     :     :     :     :     :     :                                                                                         :           +- GpuFileGpuScan parquet [loan_id#1603L,orig_channel#1604,seller_name#1605,first_home_buyer#1616,loan_purpose#1617,property_type#1618,occupancy_status#1620,property_state#1621,product_type#1624,relocation_mortgage_indicator#1627,quarter#1629] Batched: true, DataFilters: [isnotnull(loan_id#1603L), isnotnull(quarter#1629)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/acq], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,orig_channel:string,seller_name:string,first_home_buyer:string,loan_purpose...\n",
      "      :     :     :     :     :     :     :     :     :                                                                                         +- GpuColumnarExchange gpuhashpartitioning(from_seller_name#2569, 192), ENSURE_REQUIREMENTS, [id=#1127]\n",
      "      :     :     :     :     :     :     :     :     :                                                                                            +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :                                                                                               +- GpuFilter gpuisnotnull(from_seller_name#2569), true\n",
      "      :     :     :     :     :     :     :     :     :                                                                                                  +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :     :                                                                                                     +- *(7) Scan ExistingRDD[from_seller_name#2569,to_seller_name#2570]\n",
      "      :     :     :     :     :     :     :     :     +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true]),false), [id=#3701]\n",
      "      :     :     :     :     :     :     :     :        +- GpuProject [data#2945 AS first_home_buyer#3215, id#2956]\n",
      "      :     :     :     :     :     :     :     :           +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :              +- GpuFilter ((column_id#2942 = 1) AND gpuisnotnull(data#2945)), true\n",
      "      :     :     :     :     :     :     :     :                 +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :                    +- InMemoryTableScan [column_id#2942, data#2945, id#2956], [(column_id#2942 = 1), isnotnull(data#2945)]\n",
      "      :     :     :     :     :     :     :     :                          +- InMemoryRelation [column_id#2942, data#2945, id#2956], StorageLevel(disk, memory, deserialized, 1 replicas)\n",
      "      :     :     :     :     :     :     :     :                                +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :                                   +- GpuProject [column_id#2942, data#2945, id#2956]\n",
      "      :     :     :     :     :     :     :     :                                      +- GpuRunningWindow [column_id#2942, data#2945, count#2951L, gpurownumber$() gpuwindowspecdefinition(column_id#2942, count#2951L DESC NULLS LAST, gpuspecifiedwindowframe(RowFrame, gpuspecialframeboundary(unboundedpreceding$()), gpuspecialframeboundary(currentrow$()))) AS id#2956], [column_id#2942], [count#2951L DESC NULLS LAST]\n",
      "      :     :     :     :     :     :     :     :                                         +- GpuSort [column_id#2942 ASC NULLS FIRST, count#2951L DESC NULLS LAST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :     :     :     :     :     :                                            +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :                                               +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, 192), ENSURE_REQUIREMENTS, [id=#1141]\n",
      "      :     :     :     :     :     :     :     :                                                  +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :     :     :     :     :     :                                                     +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :                                                        +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, data#2945, 192), ENSURE_REQUIREMENTS, [id=#1138]\n",
      "      :     :     :     :     :     :     :     :                                                           +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[partial_gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :     :     :     :     :     :                                                              +- GpuProject [pos#2938 AS column_id#2942, col#2939 AS data#2945]\n",
      "      :     :     :     :     :     :     :     :                                                                 +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :                                                                    +- GpuFilter gpuisnotnull(col#2939), true\n",
      "      :     :     :     :     :     :     :     :                                                                       +- GpuGenerate gpuposexplode(array(orig_channel#1604, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, seller_name#2689, mod_flag#1550)), false, [pos#2938, col#2939]\n",
      "      :     :     :     :     :     :     :     :                                                                          +- GpuProject [mod_flag#1550, orig_channel#1604, seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627]\n",
      "      :     :     :     :     :     :     :     :                                                                             +- GpuShuffledHashJoin [loan_id#1539L, quarter#1570], [loan_id#1603L, quarter#1629], Inner, GpuBuildRight, false\n",
      "      :     :     :     :     :     :     :     :                                                                                :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :                                                                                :  +- GpuColumnarExchange gpuhashpartitioning(loan_id#1539L, quarter#1570, 192), ENSURE_REQUIREMENTS, [id=#1121]\n",
      "      :     :     :     :     :     :     :     :                                                                                :     +- GpuProject [quarter#1570, loan_id#1539L, mod_flag#1550]\n",
      "      :     :     :     :     :     :     :     :                                                                                :        +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :                                                                                :           +- SortMergeJoin [quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint)], [quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L], LeftOuter\n",
      "      :     :     :     :     :     :     :     :                                                                                :              :- *(2) Sort [quarter#1570 ASC NULLS FIRST, loan_id#1539L ASC NULLS FIRST, cast(timestamp_year#2417 as bigint) ASC NULLS FIRST, cast(timestamp_month#2381 as bigint) ASC NULLS FIRST], false, 0\n",
      "      :     :     :     :     :     :     :     :                                                                                :              :  +- Exchange hashpartitioning(quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint), 192), ENSURE_REQUIREMENTS, [id=#1080]\n",
      "      :     :     :     :     :     :     :     :                                                                                :              :     +- *(1) Project [loan_id#1539L, mod_flag#1550, quarter#1570, month(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2381, year(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2417]\n",
      "      :     :     :     :     :     :     :     :                                                                                :              :        +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :                                                                                :              :           +- GpuFilter (gpuisnotnull(loan_id#1539L) AND gpuisnotnull(quarter#1570)), true\n",
      "      :     :     :     :     :     :     :     :                                                                                :              :              +- GpuFileGpuScan parquet [loan_id#1539L,monthly_reporting_period#1540,mod_flag#1550,quarter#1570] Batched: true, DataFilters: [isnotnull(loan_id#1539L), isnotnull(quarter#1570)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,mod_flag:string,quarter:string>\n",
      "      :     :     :     :     :     :     :     :                                                                                :              +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :                                                                                :                 +- GpuSort [quarter#2484 ASC NULLS FIRST, loan_id#2453L ASC NULLS FIRST, timestamp_year#2307L ASC NULLS FIRST, timestamp_month#2336L ASC NULLS FIRST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :     :     :     :     :     :                                                                                :                    +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :                                                                                :                       +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L, 192), ENSURE_REQUIREMENTS, [id=#1114]\n",
      "      :     :     :     :     :     :     :     :                                                                                :                          +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :                                                                                :                             +- *(6) HashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[])\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                   +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                      +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248, 192), ENSURE_REQUIREMENTS, [id=#1107]\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                         +- GpuHashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[]), filters=ArrayBuffer())\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                            +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                               +- *(5) Project [quarter#2484, FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) AS josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, loan_id#2453L, month_y#2248]\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                  +- *(5) Filter (isnotnull(FLOOR((cast(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast((month_y#2248 - 1) as bigint)) as double) / 12.0))) AND isnotnull(CASE WHEN (pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) = 0) THEN 12 ELSE pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) END))\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                     +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                        +- GpuGenerate gpuexplode([0,1,2,3,4,5,6,7,8,9,10,11]), [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997], false, [month_y#2248]\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                           +- GpuProject [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997]\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                              +- GpuBroadcastHashJoin [loan_id#2453L, quarter#2484], [loan_id#2202L, quarter#2233], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                 :- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                 :  +- *(3) Project [quarter#2484, loan_id#2453L, month(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2051, year(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2087]\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                 :     +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                 :        +- GpuFilter (gpuisnotnull(quarter#2484) AND gpuisnotnull(loan_id#2453L)), true\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                 :           +- GpuFileGpuScan parquet [loan_id#2453L,monthly_reporting_period#2454,quarter#2484] Batched: true, DataFilters: [isnotnull(quarter#2484), isnotnull(loan_id#2453L)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(quarter), IsNotNull(loan_id)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,quarter:string>\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                 +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[1, bigint, true], input[0, string, true]),false), [id=#1096]\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                    +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[gpumax(current_loan_delinquency_status#2212), gpumin(delinquency_30#1975), gpumin(delinquency_90#1976), gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                       +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                          +- GpuColumnarExchange gpuhashpartitioning(quarter#2233, loan_id#2202L, 192), ENSURE_REQUIREMENTS, [id=#1093]\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                             +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[partial_gpumax(current_loan_delinquency_status#2212), partial_gpumin(delinquency_30#1975), partial_gpumin(delinquency_90#1976), partial_gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                                +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                                   +- *(4) Project [quarter#2233, loan_id#2202L, current_loan_delinquency_status#2212, CASE WHEN (current_loan_delinquency_status#2212 >= 1) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_30#1975, CASE WHEN (current_loan_delinquency_status#2212 >= 3) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_90#1976, CASE WHEN (current_loan_delinquency_status#2212 >= 6) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_180#1977]\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                                      +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                                         +- GpuFilter (gpuisnotnull(loan_id#2202L) AND gpuisnotnull(quarter#2233)), true\n",
      "      :     :     :     :     :     :     :     :                                                                                :                                                                                            +- GpuFileGpuScan parquet [loan_id#2202L,monthly_reporting_period#2203,current_loan_delinquency_status#2212,quarter#2233] Batched: true, DataFilters: [isnotnull(loan_id#2202L), isnotnull(quarter#2233)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,current_loan_delinquency_status:int,quarter...\n",
      "      :     :     :     :     :     :     :     :                                                                                +- GpuColumnarExchange gpuhashpartitioning(loan_id#1603L, quarter#1629, 192), ENSURE_REQUIREMENTS, [id=#1130]\n",
      "      :     :     :     :     :     :     :     :                                                                                   +- GpuProject [loan_id#1603L, orig_channel#1604, gpucoalesce(to_seller_name#2570, seller_name#1605) AS seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, quarter#1629]\n",
      "      :     :     :     :     :     :     :     :                                                                                      +- GpuShuffledHashJoin [seller_name#1605], [from_seller_name#2569], LeftOuter, GpuBuildRight, false\n",
      "      :     :     :     :     :     :     :     :                                                                                         :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :     :                                                                                         :  +- GpuColumnarExchange gpuhashpartitioning(seller_name#1605, 192), ENSURE_REQUIREMENTS, [id=#862]\n",
      "      :     :     :     :     :     :     :     :                                                                                         :     +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :                                                                                         :        +- GpuFilter (gpuisnotnull(loan_id#1603L) AND gpuisnotnull(quarter#1629)), true\n",
      "      :     :     :     :     :     :     :     :                                                                                         :           +- GpuFileGpuScan parquet [loan_id#1603L,orig_channel#1604,seller_name#1605,first_home_buyer#1616,loan_purpose#1617,property_type#1618,occupancy_status#1620,property_state#1621,product_type#1624,relocation_mortgage_indicator#1627,quarter#1629] Batched: true, DataFilters: [isnotnull(loan_id#1603L), isnotnull(quarter#1629)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/acq], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,orig_channel:string,seller_name:string,first_home_buyer:string,loan_purpose...\n",
      "      :     :     :     :     :     :     :     :                                                                                         +- GpuColumnarExchange gpuhashpartitioning(from_seller_name#2569, 192), ENSURE_REQUIREMENTS, [id=#1127]\n",
      "      :     :     :     :     :     :     :     :                                                                                            +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :                                                                                               +- GpuFilter gpuisnotnull(from_seller_name#2569), true\n",
      "      :     :     :     :     :     :     :     :                                                                                                  +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :     :                                                                                                     +- *(7) Scan ExistingRDD[from_seller_name#2569,to_seller_name#2570]\n",
      "      :     :     :     :     :     :     :     +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true]),false), [id=#3708]\n",
      "      :     :     :     :     :     :     :        +- GpuProject [data#2945 AS loan_purpose#3420, id#2956]\n",
      "      :     :     :     :     :     :     :           +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :              +- GpuFilter ((column_id#2942 = 2) AND gpuisnotnull(data#2945)), true\n",
      "      :     :     :     :     :     :     :                 +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :                    +- InMemoryTableScan [column_id#2942, data#2945, id#2956], [(column_id#2942 = 2), isnotnull(data#2945)]\n",
      "      :     :     :     :     :     :     :                          +- InMemoryRelation [column_id#2942, data#2945, id#2956], StorageLevel(disk, memory, deserialized, 1 replicas)\n",
      "      :     :     :     :     :     :     :                                +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :                                   +- GpuProject [column_id#2942, data#2945, id#2956]\n",
      "      :     :     :     :     :     :     :                                      +- GpuRunningWindow [column_id#2942, data#2945, count#2951L, gpurownumber$() gpuwindowspecdefinition(column_id#2942, count#2951L DESC NULLS LAST, gpuspecifiedwindowframe(RowFrame, gpuspecialframeboundary(unboundedpreceding$()), gpuspecialframeboundary(currentrow$()))) AS id#2956], [column_id#2942], [count#2951L DESC NULLS LAST]\n",
      "      :     :     :     :     :     :     :                                         +- GpuSort [column_id#2942 ASC NULLS FIRST, count#2951L DESC NULLS LAST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :     :     :     :     :                                            +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :                                               +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, 192), ENSURE_REQUIREMENTS, [id=#1141]\n",
      "      :     :     :     :     :     :     :                                                  +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :     :     :     :     :                                                     +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :                                                        +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, data#2945, 192), ENSURE_REQUIREMENTS, [id=#1138]\n",
      "      :     :     :     :     :     :     :                                                           +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[partial_gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :     :     :     :     :                                                              +- GpuProject [pos#2938 AS column_id#2942, col#2939 AS data#2945]\n",
      "      :     :     :     :     :     :     :                                                                 +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :                                                                    +- GpuFilter gpuisnotnull(col#2939), true\n",
      "      :     :     :     :     :     :     :                                                                       +- GpuGenerate gpuposexplode(array(orig_channel#1604, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, seller_name#2689, mod_flag#1550)), false, [pos#2938, col#2939]\n",
      "      :     :     :     :     :     :     :                                                                          +- GpuProject [mod_flag#1550, orig_channel#1604, seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627]\n",
      "      :     :     :     :     :     :     :                                                                             +- GpuShuffledHashJoin [loan_id#1539L, quarter#1570], [loan_id#1603L, quarter#1629], Inner, GpuBuildRight, false\n",
      "      :     :     :     :     :     :     :                                                                                :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :                                                                                :  +- GpuColumnarExchange gpuhashpartitioning(loan_id#1539L, quarter#1570, 192), ENSURE_REQUIREMENTS, [id=#1121]\n",
      "      :     :     :     :     :     :     :                                                                                :     +- GpuProject [quarter#1570, loan_id#1539L, mod_flag#1550]\n",
      "      :     :     :     :     :     :     :                                                                                :        +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :                                                                                :           +- SortMergeJoin [quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint)], [quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L], LeftOuter\n",
      "      :     :     :     :     :     :     :                                                                                :              :- *(2) Sort [quarter#1570 ASC NULLS FIRST, loan_id#1539L ASC NULLS FIRST, cast(timestamp_year#2417 as bigint) ASC NULLS FIRST, cast(timestamp_month#2381 as bigint) ASC NULLS FIRST], false, 0\n",
      "      :     :     :     :     :     :     :                                                                                :              :  +- Exchange hashpartitioning(quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint), 192), ENSURE_REQUIREMENTS, [id=#1080]\n",
      "      :     :     :     :     :     :     :                                                                                :              :     +- *(1) Project [loan_id#1539L, mod_flag#1550, quarter#1570, month(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2381, year(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2417]\n",
      "      :     :     :     :     :     :     :                                                                                :              :        +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :                                                                                :              :           +- GpuFilter (gpuisnotnull(loan_id#1539L) AND gpuisnotnull(quarter#1570)), true\n",
      "      :     :     :     :     :     :     :                                                                                :              :              +- GpuFileGpuScan parquet [loan_id#1539L,monthly_reporting_period#1540,mod_flag#1550,quarter#1570] Batched: true, DataFilters: [isnotnull(loan_id#1539L), isnotnull(quarter#1570)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,mod_flag:string,quarter:string>\n",
      "      :     :     :     :     :     :     :                                                                                :              +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :                                                                                :                 +- GpuSort [quarter#2484 ASC NULLS FIRST, loan_id#2453L ASC NULLS FIRST, timestamp_year#2307L ASC NULLS FIRST, timestamp_month#2336L ASC NULLS FIRST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :     :     :     :     :                                                                                :                    +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :                                                                                :                       +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L, 192), ENSURE_REQUIREMENTS, [id=#1114]\n",
      "      :     :     :     :     :     :     :                                                                                :                          +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :                                                                                :                             +- *(6) HashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[])\n",
      "      :     :     :     :     :     :     :                                                                                :                                +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :                                                                                :                                   +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :                                                                                :                                      +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248, 192), ENSURE_REQUIREMENTS, [id=#1107]\n",
      "      :     :     :     :     :     :     :                                                                                :                                         +- GpuHashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[]), filters=ArrayBuffer())\n",
      "      :     :     :     :     :     :     :                                                                                :                                            +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :                                                                                :                                               +- *(5) Project [quarter#2484, FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) AS josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, loan_id#2453L, month_y#2248]\n",
      "      :     :     :     :     :     :     :                                                                                :                                                  +- *(5) Filter (isnotnull(FLOOR((cast(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast((month_y#2248 - 1) as bigint)) as double) / 12.0))) AND isnotnull(CASE WHEN (pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) = 0) THEN 12 ELSE pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) END))\n",
      "      :     :     :     :     :     :     :                                                                                :                                                     +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :                                                                                :                                                        +- GpuGenerate gpuexplode([0,1,2,3,4,5,6,7,8,9,10,11]), [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997], false, [month_y#2248]\n",
      "      :     :     :     :     :     :     :                                                                                :                                                           +- GpuProject [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997]\n",
      "      :     :     :     :     :     :     :                                                                                :                                                              +- GpuBroadcastHashJoin [loan_id#2453L, quarter#2484], [loan_id#2202L, quarter#2233], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                 :- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                 :  +- *(3) Project [quarter#2484, loan_id#2453L, month(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2051, year(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2087]\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                 :     +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                 :        +- GpuFilter (gpuisnotnull(quarter#2484) AND gpuisnotnull(loan_id#2453L)), true\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                 :           +- GpuFileGpuScan parquet [loan_id#2453L,monthly_reporting_period#2454,quarter#2484] Batched: true, DataFilters: [isnotnull(quarter#2484), isnotnull(loan_id#2453L)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(quarter), IsNotNull(loan_id)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,quarter:string>\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                 +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[1, bigint, true], input[0, string, true]),false), [id=#1096]\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                    +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[gpumax(current_loan_delinquency_status#2212), gpumin(delinquency_30#1975), gpumin(delinquency_90#1976), gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                       +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                          +- GpuColumnarExchange gpuhashpartitioning(quarter#2233, loan_id#2202L, 192), ENSURE_REQUIREMENTS, [id=#1093]\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                             +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[partial_gpumax(current_loan_delinquency_status#2212), partial_gpumin(delinquency_30#1975), partial_gpumin(delinquency_90#1976), partial_gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                                +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                                   +- *(4) Project [quarter#2233, loan_id#2202L, current_loan_delinquency_status#2212, CASE WHEN (current_loan_delinquency_status#2212 >= 1) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_30#1975, CASE WHEN (current_loan_delinquency_status#2212 >= 3) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_90#1976, CASE WHEN (current_loan_delinquency_status#2212 >= 6) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_180#1977]\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                                      +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                                         +- GpuFilter (gpuisnotnull(loan_id#2202L) AND gpuisnotnull(quarter#2233)), true\n",
      "      :     :     :     :     :     :     :                                                                                :                                                                                            +- GpuFileGpuScan parquet [loan_id#2202L,monthly_reporting_period#2203,current_loan_delinquency_status#2212,quarter#2233] Batched: true, DataFilters: [isnotnull(loan_id#2202L), isnotnull(quarter#2233)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,current_loan_delinquency_status:int,quarter...\n",
      "      :     :     :     :     :     :     :                                                                                +- GpuColumnarExchange gpuhashpartitioning(loan_id#1603L, quarter#1629, 192), ENSURE_REQUIREMENTS, [id=#1130]\n",
      "      :     :     :     :     :     :     :                                                                                   +- GpuProject [loan_id#1603L, orig_channel#1604, gpucoalesce(to_seller_name#2570, seller_name#1605) AS seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, quarter#1629]\n",
      "      :     :     :     :     :     :     :                                                                                      +- GpuShuffledHashJoin [seller_name#1605], [from_seller_name#2569], LeftOuter, GpuBuildRight, false\n",
      "      :     :     :     :     :     :     :                                                                                         :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :     :                                                                                         :  +- GpuColumnarExchange gpuhashpartitioning(seller_name#1605, 192), ENSURE_REQUIREMENTS, [id=#862]\n",
      "      :     :     :     :     :     :     :                                                                                         :     +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :                                                                                         :        +- GpuFilter (gpuisnotnull(loan_id#1603L) AND gpuisnotnull(quarter#1629)), true\n",
      "      :     :     :     :     :     :     :                                                                                         :           +- GpuFileGpuScan parquet [loan_id#1603L,orig_channel#1604,seller_name#1605,first_home_buyer#1616,loan_purpose#1617,property_type#1618,occupancy_status#1620,property_state#1621,product_type#1624,relocation_mortgage_indicator#1627,quarter#1629] Batched: true, DataFilters: [isnotnull(loan_id#1603L), isnotnull(quarter#1629)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/acq], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,orig_channel:string,seller_name:string,first_home_buyer:string,loan_purpose...\n",
      "      :     :     :     :     :     :     :                                                                                         +- GpuColumnarExchange gpuhashpartitioning(from_seller_name#2569, 192), ENSURE_REQUIREMENTS, [id=#1127]\n",
      "      :     :     :     :     :     :     :                                                                                            +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :     :                                                                                               +- GpuFilter gpuisnotnull(from_seller_name#2569), true\n",
      "      :     :     :     :     :     :     :                                                                                                  +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :     :                                                                                                     +- *(7) Scan ExistingRDD[from_seller_name#2569,to_seller_name#2570]\n",
      "      :     :     :     :     :     :     +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true]),false), [id=#3715]\n",
      "      :     :     :     :     :     :        +- GpuProject [data#2945 AS property_type#3625, id#2956]\n",
      "      :     :     :     :     :     :           +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :              +- GpuFilter ((column_id#2942 = 3) AND gpuisnotnull(data#2945)), true\n",
      "      :     :     :     :     :     :                 +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :                    +- InMemoryTableScan [column_id#2942, data#2945, id#2956], [(column_id#2942 = 3), isnotnull(data#2945)]\n",
      "      :     :     :     :     :     :                          +- InMemoryRelation [column_id#2942, data#2945, id#2956], StorageLevel(disk, memory, deserialized, 1 replicas)\n",
      "      :     :     :     :     :     :                                +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :                                   +- GpuProject [column_id#2942, data#2945, id#2956]\n",
      "      :     :     :     :     :     :                                      +- GpuRunningWindow [column_id#2942, data#2945, count#2951L, gpurownumber$() gpuwindowspecdefinition(column_id#2942, count#2951L DESC NULLS LAST, gpuspecifiedwindowframe(RowFrame, gpuspecialframeboundary(unboundedpreceding$()), gpuspecialframeboundary(currentrow$()))) AS id#2956], [column_id#2942], [count#2951L DESC NULLS LAST]\n",
      "      :     :     :     :     :     :                                         +- GpuSort [column_id#2942 ASC NULLS FIRST, count#2951L DESC NULLS LAST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :     :     :     :                                            +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :                                               +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, 192), ENSURE_REQUIREMENTS, [id=#1141]\n",
      "      :     :     :     :     :     :                                                  +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :     :     :     :                                                     +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :                                                        +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, data#2945, 192), ENSURE_REQUIREMENTS, [id=#1138]\n",
      "      :     :     :     :     :     :                                                           +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[partial_gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :     :     :     :                                                              +- GpuProject [pos#2938 AS column_id#2942, col#2939 AS data#2945]\n",
      "      :     :     :     :     :     :                                                                 +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :                                                                    +- GpuFilter gpuisnotnull(col#2939), true\n",
      "      :     :     :     :     :     :                                                                       +- GpuGenerate gpuposexplode(array(orig_channel#1604, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, seller_name#2689, mod_flag#1550)), false, [pos#2938, col#2939]\n",
      "      :     :     :     :     :     :                                                                          +- GpuProject [mod_flag#1550, orig_channel#1604, seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627]\n",
      "      :     :     :     :     :     :                                                                             +- GpuShuffledHashJoin [loan_id#1539L, quarter#1570], [loan_id#1603L, quarter#1629], Inner, GpuBuildRight, false\n",
      "      :     :     :     :     :     :                                                                                :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :                                                                                :  +- GpuColumnarExchange gpuhashpartitioning(loan_id#1539L, quarter#1570, 192), ENSURE_REQUIREMENTS, [id=#1121]\n",
      "      :     :     :     :     :     :                                                                                :     +- GpuProject [quarter#1570, loan_id#1539L, mod_flag#1550]\n",
      "      :     :     :     :     :     :                                                                                :        +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :                                                                                :           +- SortMergeJoin [quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint)], [quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L], LeftOuter\n",
      "      :     :     :     :     :     :                                                                                :              :- *(2) Sort [quarter#1570 ASC NULLS FIRST, loan_id#1539L ASC NULLS FIRST, cast(timestamp_year#2417 as bigint) ASC NULLS FIRST, cast(timestamp_month#2381 as bigint) ASC NULLS FIRST], false, 0\n",
      "      :     :     :     :     :     :                                                                                :              :  +- Exchange hashpartitioning(quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint), 192), ENSURE_REQUIREMENTS, [id=#1080]\n",
      "      :     :     :     :     :     :                                                                                :              :     +- *(1) Project [loan_id#1539L, mod_flag#1550, quarter#1570, month(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2381, year(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2417]\n",
      "      :     :     :     :     :     :                                                                                :              :        +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :                                                                                :              :           +- GpuFilter (gpuisnotnull(loan_id#1539L) AND gpuisnotnull(quarter#1570)), true\n",
      "      :     :     :     :     :     :                                                                                :              :              +- GpuFileGpuScan parquet [loan_id#1539L,monthly_reporting_period#1540,mod_flag#1550,quarter#1570] Batched: true, DataFilters: [isnotnull(loan_id#1539L), isnotnull(quarter#1570)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,mod_flag:string,quarter:string>\n",
      "      :     :     :     :     :     :                                                                                :              +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :                                                                                :                 +- GpuSort [quarter#2484 ASC NULLS FIRST, loan_id#2453L ASC NULLS FIRST, timestamp_year#2307L ASC NULLS FIRST, timestamp_month#2336L ASC NULLS FIRST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :     :     :     :                                                                                :                    +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :                                                                                :                       +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L, 192), ENSURE_REQUIREMENTS, [id=#1114]\n",
      "      :     :     :     :     :     :                                                                                :                          +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :                                                                                :                             +- *(6) HashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[])\n",
      "      :     :     :     :     :     :                                                                                :                                +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :                                                                                :                                   +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :                                                                                :                                      +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248, 192), ENSURE_REQUIREMENTS, [id=#1107]\n",
      "      :     :     :     :     :     :                                                                                :                                         +- GpuHashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[]), filters=ArrayBuffer())\n",
      "      :     :     :     :     :     :                                                                                :                                            +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :                                                                                :                                               +- *(5) Project [quarter#2484, FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) AS josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, loan_id#2453L, month_y#2248]\n",
      "      :     :     :     :     :     :                                                                                :                                                  +- *(5) Filter (isnotnull(FLOOR((cast(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast((month_y#2248 - 1) as bigint)) as double) / 12.0))) AND isnotnull(CASE WHEN (pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) = 0) THEN 12 ELSE pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) END))\n",
      "      :     :     :     :     :     :                                                                                :                                                     +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :                                                                                :                                                        +- GpuGenerate gpuexplode([0,1,2,3,4,5,6,7,8,9,10,11]), [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997], false, [month_y#2248]\n",
      "      :     :     :     :     :     :                                                                                :                                                           +- GpuProject [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997]\n",
      "      :     :     :     :     :     :                                                                                :                                                              +- GpuBroadcastHashJoin [loan_id#2453L, quarter#2484], [loan_id#2202L, quarter#2233], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :     :     :                                                                                :                                                                 :- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :                                                                                :                                                                 :  +- *(3) Project [quarter#2484, loan_id#2453L, month(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2051, year(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2087]\n",
      "      :     :     :     :     :     :                                                                                :                                                                 :     +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :                                                                                :                                                                 :        +- GpuFilter (gpuisnotnull(quarter#2484) AND gpuisnotnull(loan_id#2453L)), true\n",
      "      :     :     :     :     :     :                                                                                :                                                                 :           +- GpuFileGpuScan parquet [loan_id#2453L,monthly_reporting_period#2454,quarter#2484] Batched: true, DataFilters: [isnotnull(quarter#2484), isnotnull(loan_id#2453L)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(quarter), IsNotNull(loan_id)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,quarter:string>\n",
      "      :     :     :     :     :     :                                                                                :                                                                 +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[1, bigint, true], input[0, string, true]),false), [id=#1096]\n",
      "      :     :     :     :     :     :                                                                                :                                                                    +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[gpumax(current_loan_delinquency_status#2212), gpumin(delinquency_30#1975), gpumin(delinquency_90#1976), gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :     :     :                                                                                :                                                                       +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :                                                                                :                                                                          +- GpuColumnarExchange gpuhashpartitioning(quarter#2233, loan_id#2202L, 192), ENSURE_REQUIREMENTS, [id=#1093]\n",
      "      :     :     :     :     :     :                                                                                :                                                                             +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[partial_gpumax(current_loan_delinquency_status#2212), partial_gpumin(delinquency_30#1975), partial_gpumin(delinquency_90#1976), partial_gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :     :     :                                                                                :                                                                                +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :                                                                                :                                                                                   +- *(4) Project [quarter#2233, loan_id#2202L, current_loan_delinquency_status#2212, CASE WHEN (current_loan_delinquency_status#2212 >= 1) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_30#1975, CASE WHEN (current_loan_delinquency_status#2212 >= 3) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_90#1976, CASE WHEN (current_loan_delinquency_status#2212 >= 6) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_180#1977]\n",
      "      :     :     :     :     :     :                                                                                :                                                                                      +- GpuColumnarToRow false\n",
      "      :     :     :     :     :     :                                                                                :                                                                                         +- GpuFilter (gpuisnotnull(loan_id#2202L) AND gpuisnotnull(quarter#2233)), true\n",
      "      :     :     :     :     :     :                                                                                :                                                                                            +- GpuFileGpuScan parquet [loan_id#2202L,monthly_reporting_period#2203,current_loan_delinquency_status#2212,quarter#2233] Batched: true, DataFilters: [isnotnull(loan_id#2202L), isnotnull(quarter#2233)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,current_loan_delinquency_status:int,quarter...\n",
      "      :     :     :     :     :     :                                                                                +- GpuColumnarExchange gpuhashpartitioning(loan_id#1603L, quarter#1629, 192), ENSURE_REQUIREMENTS, [id=#1130]\n",
      "      :     :     :     :     :     :                                                                                   +- GpuProject [loan_id#1603L, orig_channel#1604, gpucoalesce(to_seller_name#2570, seller_name#1605) AS seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, quarter#1629]\n",
      "      :     :     :     :     :     :                                                                                      +- GpuShuffledHashJoin [seller_name#1605], [from_seller_name#2569], LeftOuter, GpuBuildRight, false\n",
      "      :     :     :     :     :     :                                                                                         :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :     :                                                                                         :  +- GpuColumnarExchange gpuhashpartitioning(seller_name#1605, 192), ENSURE_REQUIREMENTS, [id=#862]\n",
      "      :     :     :     :     :     :                                                                                         :     +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :                                                                                         :        +- GpuFilter (gpuisnotnull(loan_id#1603L) AND gpuisnotnull(quarter#1629)), true\n",
      "      :     :     :     :     :     :                                                                                         :           +- GpuFileGpuScan parquet [loan_id#1603L,orig_channel#1604,seller_name#1605,first_home_buyer#1616,loan_purpose#1617,property_type#1618,occupancy_status#1620,property_state#1621,product_type#1624,relocation_mortgage_indicator#1627,quarter#1629] Batched: true, DataFilters: [isnotnull(loan_id#1603L), isnotnull(quarter#1629)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/acq], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,orig_channel:string,seller_name:string,first_home_buyer:string,loan_purpose...\n",
      "      :     :     :     :     :     :                                                                                         +- GpuColumnarExchange gpuhashpartitioning(from_seller_name#2569, 192), ENSURE_REQUIREMENTS, [id=#1127]\n",
      "      :     :     :     :     :     :                                                                                            +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :     :                                                                                               +- GpuFilter gpuisnotnull(from_seller_name#2569), true\n",
      "      :     :     :     :     :     :                                                                                                  +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :     :                                                                                                     +- *(7) Scan ExistingRDD[from_seller_name#2569,to_seller_name#2570]\n",
      "      :     :     :     :     :     +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true]),false), [id=#3722]\n",
      "      :     :     :     :     :        +- GpuProject [data#2945 AS occupancy_status#3830, id#2956]\n",
      "      :     :     :     :     :           +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :              +- GpuFilter ((column_id#2942 = 4) AND gpuisnotnull(data#2945)), true\n",
      "      :     :     :     :     :                 +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :                    +- InMemoryTableScan [column_id#2942, data#2945, id#2956], [(column_id#2942 = 4), isnotnull(data#2945)]\n",
      "      :     :     :     :     :                          +- InMemoryRelation [column_id#2942, data#2945, id#2956], StorageLevel(disk, memory, deserialized, 1 replicas)\n",
      "      :     :     :     :     :                                +- GpuColumnarToRow false\n",
      "      :     :     :     :     :                                   +- GpuProject [column_id#2942, data#2945, id#2956]\n",
      "      :     :     :     :     :                                      +- GpuRunningWindow [column_id#2942, data#2945, count#2951L, gpurownumber$() gpuwindowspecdefinition(column_id#2942, count#2951L DESC NULLS LAST, gpuspecifiedwindowframe(RowFrame, gpuspecialframeboundary(unboundedpreceding$()), gpuspecialframeboundary(currentrow$()))) AS id#2956], [column_id#2942], [count#2951L DESC NULLS LAST]\n",
      "      :     :     :     :     :                                         +- GpuSort [column_id#2942 ASC NULLS FIRST, count#2951L DESC NULLS LAST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :     :     :                                            +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :                                               +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, 192), ENSURE_REQUIREMENTS, [id=#1141]\n",
      "      :     :     :     :     :                                                  +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :     :     :                                                     +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :                                                        +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, data#2945, 192), ENSURE_REQUIREMENTS, [id=#1138]\n",
      "      :     :     :     :     :                                                           +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[partial_gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :     :     :                                                              +- GpuProject [pos#2938 AS column_id#2942, col#2939 AS data#2945]\n",
      "      :     :     :     :     :                                                                 +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :                                                                    +- GpuFilter gpuisnotnull(col#2939), true\n",
      "      :     :     :     :     :                                                                       +- GpuGenerate gpuposexplode(array(orig_channel#1604, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, seller_name#2689, mod_flag#1550)), false, [pos#2938, col#2939]\n",
      "      :     :     :     :     :                                                                          +- GpuProject [mod_flag#1550, orig_channel#1604, seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627]\n",
      "      :     :     :     :     :                                                                             +- GpuShuffledHashJoin [loan_id#1539L, quarter#1570], [loan_id#1603L, quarter#1629], Inner, GpuBuildRight, false\n",
      "      :     :     :     :     :                                                                                :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :                                                                                :  +- GpuColumnarExchange gpuhashpartitioning(loan_id#1539L, quarter#1570, 192), ENSURE_REQUIREMENTS, [id=#1121]\n",
      "      :     :     :     :     :                                                                                :     +- GpuProject [quarter#1570, loan_id#1539L, mod_flag#1550]\n",
      "      :     :     :     :     :                                                                                :        +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :                                                                                :           +- SortMergeJoin [quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint)], [quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L], LeftOuter\n",
      "      :     :     :     :     :                                                                                :              :- *(2) Sort [quarter#1570 ASC NULLS FIRST, loan_id#1539L ASC NULLS FIRST, cast(timestamp_year#2417 as bigint) ASC NULLS FIRST, cast(timestamp_month#2381 as bigint) ASC NULLS FIRST], false, 0\n",
      "      :     :     :     :     :                                                                                :              :  +- Exchange hashpartitioning(quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint), 192), ENSURE_REQUIREMENTS, [id=#1080]\n",
      "      :     :     :     :     :                                                                                :              :     +- *(1) Project [loan_id#1539L, mod_flag#1550, quarter#1570, month(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2381, year(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2417]\n",
      "      :     :     :     :     :                                                                                :              :        +- GpuColumnarToRow false\n",
      "      :     :     :     :     :                                                                                :              :           +- GpuFilter (gpuisnotnull(loan_id#1539L) AND gpuisnotnull(quarter#1570)), true\n",
      "      :     :     :     :     :                                                                                :              :              +- GpuFileGpuScan parquet [loan_id#1539L,monthly_reporting_period#1540,mod_flag#1550,quarter#1570] Batched: true, DataFilters: [isnotnull(loan_id#1539L), isnotnull(quarter#1570)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,mod_flag:string,quarter:string>\n",
      "      :     :     :     :     :                                                                                :              +- GpuColumnarToRow false\n",
      "      :     :     :     :     :                                                                                :                 +- GpuSort [quarter#2484 ASC NULLS FIRST, loan_id#2453L ASC NULLS FIRST, timestamp_year#2307L ASC NULLS FIRST, timestamp_month#2336L ASC NULLS FIRST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :     :     :                                                                                :                    +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :                                                                                :                       +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L, 192), ENSURE_REQUIREMENTS, [id=#1114]\n",
      "      :     :     :     :     :                                                                                :                          +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :                                                                                :                             +- *(6) HashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[])\n",
      "      :     :     :     :     :                                                                                :                                +- GpuColumnarToRow false\n",
      "      :     :     :     :     :                                                                                :                                   +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :                                                                                :                                      +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248, 192), ENSURE_REQUIREMENTS, [id=#1107]\n",
      "      :     :     :     :     :                                                                                :                                         +- GpuHashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[]), filters=ArrayBuffer())\n",
      "      :     :     :     :     :                                                                                :                                            +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :                                                                                :                                               +- *(5) Project [quarter#2484, FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) AS josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, loan_id#2453L, month_y#2248]\n",
      "      :     :     :     :     :                                                                                :                                                  +- *(5) Filter (isnotnull(FLOOR((cast(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast((month_y#2248 - 1) as bigint)) as double) / 12.0))) AND isnotnull(CASE WHEN (pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) = 0) THEN 12 ELSE pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) END))\n",
      "      :     :     :     :     :                                                                                :                                                     +- GpuColumnarToRow false\n",
      "      :     :     :     :     :                                                                                :                                                        +- GpuGenerate gpuexplode([0,1,2,3,4,5,6,7,8,9,10,11]), [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997], false, [month_y#2248]\n",
      "      :     :     :     :     :                                                                                :                                                           +- GpuProject [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997]\n",
      "      :     :     :     :     :                                                                                :                                                              +- GpuBroadcastHashJoin [loan_id#2453L, quarter#2484], [loan_id#2202L, quarter#2233], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :     :                                                                                :                                                                 :- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :                                                                                :                                                                 :  +- *(3) Project [quarter#2484, loan_id#2453L, month(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2051, year(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2087]\n",
      "      :     :     :     :     :                                                                                :                                                                 :     +- GpuColumnarToRow false\n",
      "      :     :     :     :     :                                                                                :                                                                 :        +- GpuFilter (gpuisnotnull(quarter#2484) AND gpuisnotnull(loan_id#2453L)), true\n",
      "      :     :     :     :     :                                                                                :                                                                 :           +- GpuFileGpuScan parquet [loan_id#2453L,monthly_reporting_period#2454,quarter#2484] Batched: true, DataFilters: [isnotnull(quarter#2484), isnotnull(loan_id#2453L)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(quarter), IsNotNull(loan_id)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,quarter:string>\n",
      "      :     :     :     :     :                                                                                :                                                                 +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[1, bigint, true], input[0, string, true]),false), [id=#1096]\n",
      "      :     :     :     :     :                                                                                :                                                                    +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[gpumax(current_loan_delinquency_status#2212), gpumin(delinquency_30#1975), gpumin(delinquency_90#1976), gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :     :                                                                                :                                                                       +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :                                                                                :                                                                          +- GpuColumnarExchange gpuhashpartitioning(quarter#2233, loan_id#2202L, 192), ENSURE_REQUIREMENTS, [id=#1093]\n",
      "      :     :     :     :     :                                                                                :                                                                             +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[partial_gpumax(current_loan_delinquency_status#2212), partial_gpumin(delinquency_30#1975), partial_gpumin(delinquency_90#1976), partial_gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :     :                                                                                :                                                                                +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :                                                                                :                                                                                   +- *(4) Project [quarter#2233, loan_id#2202L, current_loan_delinquency_status#2212, CASE WHEN (current_loan_delinquency_status#2212 >= 1) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_30#1975, CASE WHEN (current_loan_delinquency_status#2212 >= 3) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_90#1976, CASE WHEN (current_loan_delinquency_status#2212 >= 6) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_180#1977]\n",
      "      :     :     :     :     :                                                                                :                                                                                      +- GpuColumnarToRow false\n",
      "      :     :     :     :     :                                                                                :                                                                                         +- GpuFilter (gpuisnotnull(loan_id#2202L) AND gpuisnotnull(quarter#2233)), true\n",
      "      :     :     :     :     :                                                                                :                                                                                            +- GpuFileGpuScan parquet [loan_id#2202L,monthly_reporting_period#2203,current_loan_delinquency_status#2212,quarter#2233] Batched: true, DataFilters: [isnotnull(loan_id#2202L), isnotnull(quarter#2233)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,current_loan_delinquency_status:int,quarter...\n",
      "      :     :     :     :     :                                                                                +- GpuColumnarExchange gpuhashpartitioning(loan_id#1603L, quarter#1629, 192), ENSURE_REQUIREMENTS, [id=#1130]\n",
      "      :     :     :     :     :                                                                                   +- GpuProject [loan_id#1603L, orig_channel#1604, gpucoalesce(to_seller_name#2570, seller_name#1605) AS seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, quarter#1629]\n",
      "      :     :     :     :     :                                                                                      +- GpuShuffledHashJoin [seller_name#1605], [from_seller_name#2569], LeftOuter, GpuBuildRight, false\n",
      "      :     :     :     :     :                                                                                         :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :     :                                                                                         :  +- GpuColumnarExchange gpuhashpartitioning(seller_name#1605, 192), ENSURE_REQUIREMENTS, [id=#862]\n",
      "      :     :     :     :     :                                                                                         :     +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :                                                                                         :        +- GpuFilter (gpuisnotnull(loan_id#1603L) AND gpuisnotnull(quarter#1629)), true\n",
      "      :     :     :     :     :                                                                                         :           +- GpuFileGpuScan parquet [loan_id#1603L,orig_channel#1604,seller_name#1605,first_home_buyer#1616,loan_purpose#1617,property_type#1618,occupancy_status#1620,property_state#1621,product_type#1624,relocation_mortgage_indicator#1627,quarter#1629] Batched: true, DataFilters: [isnotnull(loan_id#1603L), isnotnull(quarter#1629)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/acq], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,orig_channel:string,seller_name:string,first_home_buyer:string,loan_purpose...\n",
      "      :     :     :     :     :                                                                                         +- GpuColumnarExchange gpuhashpartitioning(from_seller_name#2569, 192), ENSURE_REQUIREMENTS, [id=#1127]\n",
      "      :     :     :     :     :                                                                                            +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :     :                                                                                               +- GpuFilter gpuisnotnull(from_seller_name#2569), true\n",
      "      :     :     :     :     :                                                                                                  +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :     :                                                                                                     +- *(7) Scan ExistingRDD[from_seller_name#2569,to_seller_name#2570]\n",
      "      :     :     :     :     +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true]),false), [id=#3729]\n",
      "      :     :     :     :        +- GpuProject [data#2945 AS property_state#4035, id#2956]\n",
      "      :     :     :     :           +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :              +- GpuFilter ((column_id#2942 = 5) AND gpuisnotnull(data#2945)), true\n",
      "      :     :     :     :                 +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :                    +- InMemoryTableScan [column_id#2942, data#2945, id#2956], [(column_id#2942 = 5), isnotnull(data#2945)]\n",
      "      :     :     :     :                          +- InMemoryRelation [column_id#2942, data#2945, id#2956], StorageLevel(disk, memory, deserialized, 1 replicas)\n",
      "      :     :     :     :                                +- GpuColumnarToRow false\n",
      "      :     :     :     :                                   +- GpuProject [column_id#2942, data#2945, id#2956]\n",
      "      :     :     :     :                                      +- GpuRunningWindow [column_id#2942, data#2945, count#2951L, gpurownumber$() gpuwindowspecdefinition(column_id#2942, count#2951L DESC NULLS LAST, gpuspecifiedwindowframe(RowFrame, gpuspecialframeboundary(unboundedpreceding$()), gpuspecialframeboundary(currentrow$()))) AS id#2956], [column_id#2942], [count#2951L DESC NULLS LAST]\n",
      "      :     :     :     :                                         +- GpuSort [column_id#2942 ASC NULLS FIRST, count#2951L DESC NULLS LAST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :     :                                            +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :                                               +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, 192), ENSURE_REQUIREMENTS, [id=#1141]\n",
      "      :     :     :     :                                                  +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :     :                                                     +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :                                                        +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, data#2945, 192), ENSURE_REQUIREMENTS, [id=#1138]\n",
      "      :     :     :     :                                                           +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[partial_gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :     :                                                              +- GpuProject [pos#2938 AS column_id#2942, col#2939 AS data#2945]\n",
      "      :     :     :     :                                                                 +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :                                                                    +- GpuFilter gpuisnotnull(col#2939), true\n",
      "      :     :     :     :                                                                       +- GpuGenerate gpuposexplode(array(orig_channel#1604, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, seller_name#2689, mod_flag#1550)), false, [pos#2938, col#2939]\n",
      "      :     :     :     :                                                                          +- GpuProject [mod_flag#1550, orig_channel#1604, seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627]\n",
      "      :     :     :     :                                                                             +- GpuShuffledHashJoin [loan_id#1539L, quarter#1570], [loan_id#1603L, quarter#1629], Inner, GpuBuildRight, false\n",
      "      :     :     :     :                                                                                :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :                                                                                :  +- GpuColumnarExchange gpuhashpartitioning(loan_id#1539L, quarter#1570, 192), ENSURE_REQUIREMENTS, [id=#1121]\n",
      "      :     :     :     :                                                                                :     +- GpuProject [quarter#1570, loan_id#1539L, mod_flag#1550]\n",
      "      :     :     :     :                                                                                :        +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :                                                                                :           +- SortMergeJoin [quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint)], [quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L], LeftOuter\n",
      "      :     :     :     :                                                                                :              :- *(2) Sort [quarter#1570 ASC NULLS FIRST, loan_id#1539L ASC NULLS FIRST, cast(timestamp_year#2417 as bigint) ASC NULLS FIRST, cast(timestamp_month#2381 as bigint) ASC NULLS FIRST], false, 0\n",
      "      :     :     :     :                                                                                :              :  +- Exchange hashpartitioning(quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint), 192), ENSURE_REQUIREMENTS, [id=#1080]\n",
      "      :     :     :     :                                                                                :              :     +- *(1) Project [loan_id#1539L, mod_flag#1550, quarter#1570, month(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2381, year(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2417]\n",
      "      :     :     :     :                                                                                :              :        +- GpuColumnarToRow false\n",
      "      :     :     :     :                                                                                :              :           +- GpuFilter (gpuisnotnull(loan_id#1539L) AND gpuisnotnull(quarter#1570)), true\n",
      "      :     :     :     :                                                                                :              :              +- GpuFileGpuScan parquet [loan_id#1539L,monthly_reporting_period#1540,mod_flag#1550,quarter#1570] Batched: true, DataFilters: [isnotnull(loan_id#1539L), isnotnull(quarter#1570)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,mod_flag:string,quarter:string>\n",
      "      :     :     :     :                                                                                :              +- GpuColumnarToRow false\n",
      "      :     :     :     :                                                                                :                 +- GpuSort [quarter#2484 ASC NULLS FIRST, loan_id#2453L ASC NULLS FIRST, timestamp_year#2307L ASC NULLS FIRST, timestamp_month#2336L ASC NULLS FIRST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :     :                                                                                :                    +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :                                                                                :                       +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L, 192), ENSURE_REQUIREMENTS, [id=#1114]\n",
      "      :     :     :     :                                                                                :                          +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :                                                                                :                             +- *(6) HashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[])\n",
      "      :     :     :     :                                                                                :                                +- GpuColumnarToRow false\n",
      "      :     :     :     :                                                                                :                                   +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :                                                                                :                                      +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248, 192), ENSURE_REQUIREMENTS, [id=#1107]\n",
      "      :     :     :     :                                                                                :                                         +- GpuHashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[]), filters=ArrayBuffer())\n",
      "      :     :     :     :                                                                                :                                            +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :                                                                                :                                               +- *(5) Project [quarter#2484, FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) AS josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, loan_id#2453L, month_y#2248]\n",
      "      :     :     :     :                                                                                :                                                  +- *(5) Filter (isnotnull(FLOOR((cast(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast((month_y#2248 - 1) as bigint)) as double) / 12.0))) AND isnotnull(CASE WHEN (pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) = 0) THEN 12 ELSE pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) END))\n",
      "      :     :     :     :                                                                                :                                                     +- GpuColumnarToRow false\n",
      "      :     :     :     :                                                                                :                                                        +- GpuGenerate gpuexplode([0,1,2,3,4,5,6,7,8,9,10,11]), [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997], false, [month_y#2248]\n",
      "      :     :     :     :                                                                                :                                                           +- GpuProject [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997]\n",
      "      :     :     :     :                                                                                :                                                              +- GpuBroadcastHashJoin [loan_id#2453L, quarter#2484], [loan_id#2202L, quarter#2233], LeftOuter, GpuBuildRight\n",
      "      :     :     :     :                                                                                :                                                                 :- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :                                                                                :                                                                 :  +- *(3) Project [quarter#2484, loan_id#2453L, month(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2051, year(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2087]\n",
      "      :     :     :     :                                                                                :                                                                 :     +- GpuColumnarToRow false\n",
      "      :     :     :     :                                                                                :                                                                 :        +- GpuFilter (gpuisnotnull(quarter#2484) AND gpuisnotnull(loan_id#2453L)), true\n",
      "      :     :     :     :                                                                                :                                                                 :           +- GpuFileGpuScan parquet [loan_id#2453L,monthly_reporting_period#2454,quarter#2484] Batched: true, DataFilters: [isnotnull(quarter#2484), isnotnull(loan_id#2453L)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(quarter), IsNotNull(loan_id)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,quarter:string>\n",
      "      :     :     :     :                                                                                :                                                                 +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[1, bigint, true], input[0, string, true]),false), [id=#1096]\n",
      "      :     :     :     :                                                                                :                                                                    +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[gpumax(current_loan_delinquency_status#2212), gpumin(delinquency_30#1975), gpumin(delinquency_90#1976), gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :                                                                                :                                                                       +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :                                                                                :                                                                          +- GpuColumnarExchange gpuhashpartitioning(quarter#2233, loan_id#2202L, 192), ENSURE_REQUIREMENTS, [id=#1093]\n",
      "      :     :     :     :                                                                                :                                                                             +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[partial_gpumax(current_loan_delinquency_status#2212), partial_gpumin(delinquency_30#1975), partial_gpumin(delinquency_90#1976), partial_gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :     :                                                                                :                                                                                +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :                                                                                :                                                                                   +- *(4) Project [quarter#2233, loan_id#2202L, current_loan_delinquency_status#2212, CASE WHEN (current_loan_delinquency_status#2212 >= 1) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_30#1975, CASE WHEN (current_loan_delinquency_status#2212 >= 3) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_90#1976, CASE WHEN (current_loan_delinquency_status#2212 >= 6) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_180#1977]\n",
      "      :     :     :     :                                                                                :                                                                                      +- GpuColumnarToRow false\n",
      "      :     :     :     :                                                                                :                                                                                         +- GpuFilter (gpuisnotnull(loan_id#2202L) AND gpuisnotnull(quarter#2233)), true\n",
      "      :     :     :     :                                                                                :                                                                                            +- GpuFileGpuScan parquet [loan_id#2202L,monthly_reporting_period#2203,current_loan_delinquency_status#2212,quarter#2233] Batched: true, DataFilters: [isnotnull(loan_id#2202L), isnotnull(quarter#2233)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,current_loan_delinquency_status:int,quarter...\n",
      "      :     :     :     :                                                                                +- GpuColumnarExchange gpuhashpartitioning(loan_id#1603L, quarter#1629, 192), ENSURE_REQUIREMENTS, [id=#1130]\n",
      "      :     :     :     :                                                                                   +- GpuProject [loan_id#1603L, orig_channel#1604, gpucoalesce(to_seller_name#2570, seller_name#1605) AS seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, quarter#1629]\n",
      "      :     :     :     :                                                                                      +- GpuShuffledHashJoin [seller_name#1605], [from_seller_name#2569], LeftOuter, GpuBuildRight, false\n",
      "      :     :     :     :                                                                                         :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :     :                                                                                         :  +- GpuColumnarExchange gpuhashpartitioning(seller_name#1605, 192), ENSURE_REQUIREMENTS, [id=#862]\n",
      "      :     :     :     :                                                                                         :     +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :                                                                                         :        +- GpuFilter (gpuisnotnull(loan_id#1603L) AND gpuisnotnull(quarter#1629)), true\n",
      "      :     :     :     :                                                                                         :           +- GpuFileGpuScan parquet [loan_id#1603L,orig_channel#1604,seller_name#1605,first_home_buyer#1616,loan_purpose#1617,property_type#1618,occupancy_status#1620,property_state#1621,product_type#1624,relocation_mortgage_indicator#1627,quarter#1629] Batched: true, DataFilters: [isnotnull(loan_id#1603L), isnotnull(quarter#1629)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/acq], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,orig_channel:string,seller_name:string,first_home_buyer:string,loan_purpose...\n",
      "      :     :     :     :                                                                                         +- GpuColumnarExchange gpuhashpartitioning(from_seller_name#2569, 192), ENSURE_REQUIREMENTS, [id=#1127]\n",
      "      :     :     :     :                                                                                            +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :     :                                                                                               +- GpuFilter gpuisnotnull(from_seller_name#2569), true\n",
      "      :     :     :     :                                                                                                  +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :     :                                                                                                     +- *(7) Scan ExistingRDD[from_seller_name#2569,to_seller_name#2570]\n",
      "      :     :     :     +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true]),false), [id=#3736]\n",
      "      :     :     :        +- GpuProject [data#2945 AS product_type#4240, id#2956]\n",
      "      :     :     :           +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :              +- GpuFilter ((column_id#2942 = 6) AND gpuisnotnull(data#2945)), true\n",
      "      :     :     :                 +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :                    +- InMemoryTableScan [column_id#2942, data#2945, id#2956], [(column_id#2942 = 6), isnotnull(data#2945)]\n",
      "      :     :     :                          +- InMemoryRelation [column_id#2942, data#2945, id#2956], StorageLevel(disk, memory, deserialized, 1 replicas)\n",
      "      :     :     :                                +- GpuColumnarToRow false\n",
      "      :     :     :                                   +- GpuProject [column_id#2942, data#2945, id#2956]\n",
      "      :     :     :                                      +- GpuRunningWindow [column_id#2942, data#2945, count#2951L, gpurownumber$() gpuwindowspecdefinition(column_id#2942, count#2951L DESC NULLS LAST, gpuspecifiedwindowframe(RowFrame, gpuspecialframeboundary(unboundedpreceding$()), gpuspecialframeboundary(currentrow$()))) AS id#2956], [column_id#2942], [count#2951L DESC NULLS LAST]\n",
      "      :     :     :                                         +- GpuSort [column_id#2942 ASC NULLS FIRST, count#2951L DESC NULLS LAST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :                                            +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :                                               +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, 192), ENSURE_REQUIREMENTS, [id=#1141]\n",
      "      :     :     :                                                  +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :                                                     +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :                                                        +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, data#2945, 192), ENSURE_REQUIREMENTS, [id=#1138]\n",
      "      :     :     :                                                           +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[partial_gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :     :                                                              +- GpuProject [pos#2938 AS column_id#2942, col#2939 AS data#2945]\n",
      "      :     :     :                                                                 +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :                                                                    +- GpuFilter gpuisnotnull(col#2939), true\n",
      "      :     :     :                                                                       +- GpuGenerate gpuposexplode(array(orig_channel#1604, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, seller_name#2689, mod_flag#1550)), false, [pos#2938, col#2939]\n",
      "      :     :     :                                                                          +- GpuProject [mod_flag#1550, orig_channel#1604, seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627]\n",
      "      :     :     :                                                                             +- GpuShuffledHashJoin [loan_id#1539L, quarter#1570], [loan_id#1603L, quarter#1629], Inner, GpuBuildRight, false\n",
      "      :     :     :                                                                                :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :                                                                                :  +- GpuColumnarExchange gpuhashpartitioning(loan_id#1539L, quarter#1570, 192), ENSURE_REQUIREMENTS, [id=#1121]\n",
      "      :     :     :                                                                                :     +- GpuProject [quarter#1570, loan_id#1539L, mod_flag#1550]\n",
      "      :     :     :                                                                                :        +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :                                                                                :           +- SortMergeJoin [quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint)], [quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L], LeftOuter\n",
      "      :     :     :                                                                                :              :- *(2) Sort [quarter#1570 ASC NULLS FIRST, loan_id#1539L ASC NULLS FIRST, cast(timestamp_year#2417 as bigint) ASC NULLS FIRST, cast(timestamp_month#2381 as bigint) ASC NULLS FIRST], false, 0\n",
      "      :     :     :                                                                                :              :  +- Exchange hashpartitioning(quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint), 192), ENSURE_REQUIREMENTS, [id=#1080]\n",
      "      :     :     :                                                                                :              :     +- *(1) Project [loan_id#1539L, mod_flag#1550, quarter#1570, month(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2381, year(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2417]\n",
      "      :     :     :                                                                                :              :        +- GpuColumnarToRow false\n",
      "      :     :     :                                                                                :              :           +- GpuFilter (gpuisnotnull(loan_id#1539L) AND gpuisnotnull(quarter#1570)), true\n",
      "      :     :     :                                                                                :              :              +- GpuFileGpuScan parquet [loan_id#1539L,monthly_reporting_period#1540,mod_flag#1550,quarter#1570] Batched: true, DataFilters: [isnotnull(loan_id#1539L), isnotnull(quarter#1570)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,mod_flag:string,quarter:string>\n",
      "      :     :     :                                                                                :              +- GpuColumnarToRow false\n",
      "      :     :     :                                                                                :                 +- GpuSort [quarter#2484 ASC NULLS FIRST, loan_id#2453L ASC NULLS FIRST, timestamp_year#2307L ASC NULLS FIRST, timestamp_month#2336L ASC NULLS FIRST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :     :                                                                                :                    +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :                                                                                :                       +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L, 192), ENSURE_REQUIREMENTS, [id=#1114]\n",
      "      :     :     :                                                                                :                          +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :                                                                                :                             +- *(6) HashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[])\n",
      "      :     :     :                                                                                :                                +- GpuColumnarToRow false\n",
      "      :     :     :                                                                                :                                   +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :                                                                                :                                      +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248, 192), ENSURE_REQUIREMENTS, [id=#1107]\n",
      "      :     :     :                                                                                :                                         +- GpuHashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[]), filters=ArrayBuffer())\n",
      "      :     :     :                                                                                :                                            +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :                                                                                :                                               +- *(5) Project [quarter#2484, FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) AS josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, loan_id#2453L, month_y#2248]\n",
      "      :     :     :                                                                                :                                                  +- *(5) Filter (isnotnull(FLOOR((cast(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast((month_y#2248 - 1) as bigint)) as double) / 12.0))) AND isnotnull(CASE WHEN (pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) = 0) THEN 12 ELSE pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) END))\n",
      "      :     :     :                                                                                :                                                     +- GpuColumnarToRow false\n",
      "      :     :     :                                                                                :                                                        +- GpuGenerate gpuexplode([0,1,2,3,4,5,6,7,8,9,10,11]), [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997], false, [month_y#2248]\n",
      "      :     :     :                                                                                :                                                           +- GpuProject [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997]\n",
      "      :     :     :                                                                                :                                                              +- GpuBroadcastHashJoin [loan_id#2453L, quarter#2484], [loan_id#2202L, quarter#2233], LeftOuter, GpuBuildRight\n",
      "      :     :     :                                                                                :                                                                 :- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :                                                                                :                                                                 :  +- *(3) Project [quarter#2484, loan_id#2453L, month(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2051, year(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2087]\n",
      "      :     :     :                                                                                :                                                                 :     +- GpuColumnarToRow false\n",
      "      :     :     :                                                                                :                                                                 :        +- GpuFilter (gpuisnotnull(quarter#2484) AND gpuisnotnull(loan_id#2453L)), true\n",
      "      :     :     :                                                                                :                                                                 :           +- GpuFileGpuScan parquet [loan_id#2453L,monthly_reporting_period#2454,quarter#2484] Batched: true, DataFilters: [isnotnull(quarter#2484), isnotnull(loan_id#2453L)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(quarter), IsNotNull(loan_id)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,quarter:string>\n",
      "      :     :     :                                                                                :                                                                 +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[1, bigint, true], input[0, string, true]),false), [id=#1096]\n",
      "      :     :     :                                                                                :                                                                    +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[gpumax(current_loan_delinquency_status#2212), gpumin(delinquency_30#1975), gpumin(delinquency_90#1976), gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :                                                                                :                                                                       +- GpuShuffleCoalesce 536870912\n",
      "      :     :     :                                                                                :                                                                          +- GpuColumnarExchange gpuhashpartitioning(quarter#2233, loan_id#2202L, 192), ENSURE_REQUIREMENTS, [id=#1093]\n",
      "      :     :     :                                                                                :                                                                             +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[partial_gpumax(current_loan_delinquency_status#2212), partial_gpumin(delinquency_30#1975), partial_gpumin(delinquency_90#1976), partial_gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :     :                                                                                :                                                                                +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :                                                                                :                                                                                   +- *(4) Project [quarter#2233, loan_id#2202L, current_loan_delinquency_status#2212, CASE WHEN (current_loan_delinquency_status#2212 >= 1) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_30#1975, CASE WHEN (current_loan_delinquency_status#2212 >= 3) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_90#1976, CASE WHEN (current_loan_delinquency_status#2212 >= 6) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_180#1977]\n",
      "      :     :     :                                                                                :                                                                                      +- GpuColumnarToRow false\n",
      "      :     :     :                                                                                :                                                                                         +- GpuFilter (gpuisnotnull(loan_id#2202L) AND gpuisnotnull(quarter#2233)), true\n",
      "      :     :     :                                                                                :                                                                                            +- GpuFileGpuScan parquet [loan_id#2202L,monthly_reporting_period#2203,current_loan_delinquency_status#2212,quarter#2233] Batched: true, DataFilters: [isnotnull(loan_id#2202L), isnotnull(quarter#2233)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,current_loan_delinquency_status:int,quarter...\n",
      "      :     :     :                                                                                +- GpuColumnarExchange gpuhashpartitioning(loan_id#1603L, quarter#1629, 192), ENSURE_REQUIREMENTS, [id=#1130]\n",
      "      :     :     :                                                                                   +- GpuProject [loan_id#1603L, orig_channel#1604, gpucoalesce(to_seller_name#2570, seller_name#1605) AS seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, quarter#1629]\n",
      "      :     :     :                                                                                      +- GpuShuffledHashJoin [seller_name#1605], [from_seller_name#2569], LeftOuter, GpuBuildRight, false\n",
      "      :     :     :                                                                                         :- GpuShuffleCoalesce 536870912\n",
      "      :     :     :                                                                                         :  +- GpuColumnarExchange gpuhashpartitioning(seller_name#1605, 192), ENSURE_REQUIREMENTS, [id=#862]\n",
      "      :     :     :                                                                                         :     +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :                                                                                         :        +- GpuFilter (gpuisnotnull(loan_id#1603L) AND gpuisnotnull(quarter#1629)), true\n",
      "      :     :     :                                                                                         :           +- GpuFileGpuScan parquet [loan_id#1603L,orig_channel#1604,seller_name#1605,first_home_buyer#1616,loan_purpose#1617,property_type#1618,occupancy_status#1620,property_state#1621,product_type#1624,relocation_mortgage_indicator#1627,quarter#1629] Batched: true, DataFilters: [isnotnull(loan_id#1603L), isnotnull(quarter#1629)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/acq], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,orig_channel:string,seller_name:string,first_home_buyer:string,loan_purpose...\n",
      "      :     :     :                                                                                         +- GpuColumnarExchange gpuhashpartitioning(from_seller_name#2569, 192), ENSURE_REQUIREMENTS, [id=#1127]\n",
      "      :     :     :                                                                                            +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :     :                                                                                               +- GpuFilter gpuisnotnull(from_seller_name#2569), true\n",
      "      :     :     :                                                                                                  +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :     :                                                                                                     +- *(7) Scan ExistingRDD[from_seller_name#2569,to_seller_name#2570]\n",
      "      :     :     +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true]),false), [id=#3743]\n",
      "      :     :        +- GpuProject [data#2945 AS relocation_mortgage_indicator#4445, id#2956]\n",
      "      :     :           +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :              +- GpuFilter ((column_id#2942 = 7) AND gpuisnotnull(data#2945)), true\n",
      "      :     :                 +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :                    +- InMemoryTableScan [column_id#2942, data#2945, id#2956], [(column_id#2942 = 7), isnotnull(data#2945)]\n",
      "      :     :                          +- InMemoryRelation [column_id#2942, data#2945, id#2956], StorageLevel(disk, memory, deserialized, 1 replicas)\n",
      "      :     :                                +- GpuColumnarToRow false\n",
      "      :     :                                   +- GpuProject [column_id#2942, data#2945, id#2956]\n",
      "      :     :                                      +- GpuRunningWindow [column_id#2942, data#2945, count#2951L, gpurownumber$() gpuwindowspecdefinition(column_id#2942, count#2951L DESC NULLS LAST, gpuspecifiedwindowframe(RowFrame, gpuspecialframeboundary(unboundedpreceding$()), gpuspecialframeboundary(currentrow$()))) AS id#2956], [column_id#2942], [count#2951L DESC NULLS LAST]\n",
      "      :     :                                         +- GpuSort [column_id#2942 ASC NULLS FIRST, count#2951L DESC NULLS LAST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :                                            +- GpuShuffleCoalesce 536870912\n",
      "      :     :                                               +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, 192), ENSURE_REQUIREMENTS, [id=#1141]\n",
      "      :     :                                                  +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :                                                     +- GpuShuffleCoalesce 536870912\n",
      "      :     :                                                        +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, data#2945, 192), ENSURE_REQUIREMENTS, [id=#1138]\n",
      "      :     :                                                           +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[partial_gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :     :                                                              +- GpuProject [pos#2938 AS column_id#2942, col#2939 AS data#2945]\n",
      "      :     :                                                                 +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :                                                                    +- GpuFilter gpuisnotnull(col#2939), true\n",
      "      :     :                                                                       +- GpuGenerate gpuposexplode(array(orig_channel#1604, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, seller_name#2689, mod_flag#1550)), false, [pos#2938, col#2939]\n",
      "      :     :                                                                          +- GpuProject [mod_flag#1550, orig_channel#1604, seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627]\n",
      "      :     :                                                                             +- GpuShuffledHashJoin [loan_id#1539L, quarter#1570], [loan_id#1603L, quarter#1629], Inner, GpuBuildRight, false\n",
      "      :     :                                                                                :- GpuShuffleCoalesce 536870912\n",
      "      :     :                                                                                :  +- GpuColumnarExchange gpuhashpartitioning(loan_id#1539L, quarter#1570, 192), ENSURE_REQUIREMENTS, [id=#1121]\n",
      "      :     :                                                                                :     +- GpuProject [quarter#1570, loan_id#1539L, mod_flag#1550]\n",
      "      :     :                                                                                :        +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :                                                                                :           +- SortMergeJoin [quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint)], [quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L], LeftOuter\n",
      "      :     :                                                                                :              :- *(2) Sort [quarter#1570 ASC NULLS FIRST, loan_id#1539L ASC NULLS FIRST, cast(timestamp_year#2417 as bigint) ASC NULLS FIRST, cast(timestamp_month#2381 as bigint) ASC NULLS FIRST], false, 0\n",
      "      :     :                                                                                :              :  +- Exchange hashpartitioning(quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint), 192), ENSURE_REQUIREMENTS, [id=#1080]\n",
      "      :     :                                                                                :              :     +- *(1) Project [loan_id#1539L, mod_flag#1550, quarter#1570, month(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2381, year(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2417]\n",
      "      :     :                                                                                :              :        +- GpuColumnarToRow false\n",
      "      :     :                                                                                :              :           +- GpuFilter (gpuisnotnull(loan_id#1539L) AND gpuisnotnull(quarter#1570)), true\n",
      "      :     :                                                                                :              :              +- GpuFileGpuScan parquet [loan_id#1539L,monthly_reporting_period#1540,mod_flag#1550,quarter#1570] Batched: true, DataFilters: [isnotnull(loan_id#1539L), isnotnull(quarter#1570)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,mod_flag:string,quarter:string>\n",
      "      :     :                                                                                :              +- GpuColumnarToRow false\n",
      "      :     :                                                                                :                 +- GpuSort [quarter#2484 ASC NULLS FIRST, loan_id#2453L ASC NULLS FIRST, timestamp_year#2307L ASC NULLS FIRST, timestamp_month#2336L ASC NULLS FIRST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :     :                                                                                :                    +- GpuShuffleCoalesce 536870912\n",
      "      :     :                                                                                :                       +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L, 192), ENSURE_REQUIREMENTS, [id=#1114]\n",
      "      :     :                                                                                :                          +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :                                                                                :                             +- *(6) HashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[])\n",
      "      :     :                                                                                :                                +- GpuColumnarToRow false\n",
      "      :     :                                                                                :                                   +- GpuShuffleCoalesce 536870912\n",
      "      :     :                                                                                :                                      +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248, 192), ENSURE_REQUIREMENTS, [id=#1107]\n",
      "      :     :                                                                                :                                         +- GpuHashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[]), filters=ArrayBuffer())\n",
      "      :     :                                                                                :                                            +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :                                                                                :                                               +- *(5) Project [quarter#2484, FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) AS josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, loan_id#2453L, month_y#2248]\n",
      "      :     :                                                                                :                                                  +- *(5) Filter (isnotnull(FLOOR((cast(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast((month_y#2248 - 1) as bigint)) as double) / 12.0))) AND isnotnull(CASE WHEN (pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) = 0) THEN 12 ELSE pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) END))\n",
      "      :     :                                                                                :                                                     +- GpuColumnarToRow false\n",
      "      :     :                                                                                :                                                        +- GpuGenerate gpuexplode([0,1,2,3,4,5,6,7,8,9,10,11]), [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997], false, [month_y#2248]\n",
      "      :     :                                                                                :                                                           +- GpuProject [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997]\n",
      "      :     :                                                                                :                                                              +- GpuBroadcastHashJoin [loan_id#2453L, quarter#2484], [loan_id#2202L, quarter#2233], LeftOuter, GpuBuildRight\n",
      "      :     :                                                                                :                                                                 :- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :                                                                                :                                                                 :  +- *(3) Project [quarter#2484, loan_id#2453L, month(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2051, year(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2087]\n",
      "      :     :                                                                                :                                                                 :     +- GpuColumnarToRow false\n",
      "      :     :                                                                                :                                                                 :        +- GpuFilter (gpuisnotnull(quarter#2484) AND gpuisnotnull(loan_id#2453L)), true\n",
      "      :     :                                                                                :                                                                 :           +- GpuFileGpuScan parquet [loan_id#2453L,monthly_reporting_period#2454,quarter#2484] Batched: true, DataFilters: [isnotnull(quarter#2484), isnotnull(loan_id#2453L)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(quarter), IsNotNull(loan_id)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,quarter:string>\n",
      "      :     :                                                                                :                                                                 +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[1, bigint, true], input[0, string, true]),false), [id=#1096]\n",
      "      :     :                                                                                :                                                                    +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[gpumax(current_loan_delinquency_status#2212), gpumin(delinquency_30#1975), gpumin(delinquency_90#1976), gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :                                                                                :                                                                       +- GpuShuffleCoalesce 536870912\n",
      "      :     :                                                                                :                                                                          +- GpuColumnarExchange gpuhashpartitioning(quarter#2233, loan_id#2202L, 192), ENSURE_REQUIREMENTS, [id=#1093]\n",
      "      :     :                                                                                :                                                                             +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[partial_gpumax(current_loan_delinquency_status#2212), partial_gpumin(delinquency_30#1975), partial_gpumin(delinquency_90#1976), partial_gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :     :                                                                                :                                                                                +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :                                                                                :                                                                                   +- *(4) Project [quarter#2233, loan_id#2202L, current_loan_delinquency_status#2212, CASE WHEN (current_loan_delinquency_status#2212 >= 1) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_30#1975, CASE WHEN (current_loan_delinquency_status#2212 >= 3) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_90#1976, CASE WHEN (current_loan_delinquency_status#2212 >= 6) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_180#1977]\n",
      "      :     :                                                                                :                                                                                      +- GpuColumnarToRow false\n",
      "      :     :                                                                                :                                                                                         +- GpuFilter (gpuisnotnull(loan_id#2202L) AND gpuisnotnull(quarter#2233)), true\n",
      "      :     :                                                                                :                                                                                            +- GpuFileGpuScan parquet [loan_id#2202L,monthly_reporting_period#2203,current_loan_delinquency_status#2212,quarter#2233] Batched: true, DataFilters: [isnotnull(loan_id#2202L), isnotnull(quarter#2233)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,current_loan_delinquency_status:int,quarter...\n",
      "      :     :                                                                                +- GpuColumnarExchange gpuhashpartitioning(loan_id#1603L, quarter#1629, 192), ENSURE_REQUIREMENTS, [id=#1130]\n",
      "      :     :                                                                                   +- GpuProject [loan_id#1603L, orig_channel#1604, gpucoalesce(to_seller_name#2570, seller_name#1605) AS seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, quarter#1629]\n",
      "      :     :                                                                                      +- GpuShuffledHashJoin [seller_name#1605], [from_seller_name#2569], LeftOuter, GpuBuildRight, false\n",
      "      :     :                                                                                         :- GpuShuffleCoalesce 536870912\n",
      "      :     :                                                                                         :  +- GpuColumnarExchange gpuhashpartitioning(seller_name#1605, 192), ENSURE_REQUIREMENTS, [id=#862]\n",
      "      :     :                                                                                         :     +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :                                                                                         :        +- GpuFilter (gpuisnotnull(loan_id#1603L) AND gpuisnotnull(quarter#1629)), true\n",
      "      :     :                                                                                         :           +- GpuFileGpuScan parquet [loan_id#1603L,orig_channel#1604,seller_name#1605,first_home_buyer#1616,loan_purpose#1617,property_type#1618,occupancy_status#1620,property_state#1621,product_type#1624,relocation_mortgage_indicator#1627,quarter#1629] Batched: true, DataFilters: [isnotnull(loan_id#1603L), isnotnull(quarter#1629)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/acq], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,orig_channel:string,seller_name:string,first_home_buyer:string,loan_purpose...\n",
      "      :     :                                                                                         +- GpuColumnarExchange gpuhashpartitioning(from_seller_name#2569, 192), ENSURE_REQUIREMENTS, [id=#1127]\n",
      "      :     :                                                                                            +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :     :                                                                                               +- GpuFilter gpuisnotnull(from_seller_name#2569), true\n",
      "      :     :                                                                                                  +- GpuRowToColumnar targetsize(536870912)\n",
      "      :     :                                                                                                     +- *(7) Scan ExistingRDD[from_seller_name#2569,to_seller_name#2570]\n",
      "      :     +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true]),false), [id=#3750]\n",
      "      :        +- GpuProject [data#2945 AS seller_name#4650, id#2956]\n",
      "      :           +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :              +- GpuFilter ((column_id#2942 = 8) AND gpuisnotnull(data#2945)), true\n",
      "      :                 +- GpuRowToColumnar targetsize(536870912)\n",
      "      :                    +- InMemoryTableScan [column_id#2942, data#2945, id#2956], [(column_id#2942 = 8), isnotnull(data#2945)]\n",
      "      :                          +- InMemoryRelation [column_id#2942, data#2945, id#2956], StorageLevel(disk, memory, deserialized, 1 replicas)\n",
      "      :                                +- GpuColumnarToRow false\n",
      "      :                                   +- GpuProject [column_id#2942, data#2945, id#2956]\n",
      "      :                                      +- GpuRunningWindow [column_id#2942, data#2945, count#2951L, gpurownumber$() gpuwindowspecdefinition(column_id#2942, count#2951L DESC NULLS LAST, gpuspecifiedwindowframe(RowFrame, gpuspecialframeboundary(unboundedpreceding$()), gpuspecialframeboundary(currentrow$()))) AS id#2956], [column_id#2942], [count#2951L DESC NULLS LAST]\n",
      "      :                                         +- GpuSort [column_id#2942 ASC NULLS FIRST, count#2951L DESC NULLS LAST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :                                            +- GpuShuffleCoalesce 536870912\n",
      "      :                                               +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, 192), ENSURE_REQUIREMENTS, [id=#1141]\n",
      "      :                                                  +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :                                                     +- GpuShuffleCoalesce 536870912\n",
      "      :                                                        +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, data#2945, 192), ENSURE_REQUIREMENTS, [id=#1138]\n",
      "      :                                                           +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[partial_gpucount(1)]), filters=ArrayBuffer(None))\n",
      "      :                                                              +- GpuProject [pos#2938 AS column_id#2942, col#2939 AS data#2945]\n",
      "      :                                                                 +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :                                                                    +- GpuFilter gpuisnotnull(col#2939), true\n",
      "      :                                                                       +- GpuGenerate gpuposexplode(array(orig_channel#1604, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, seller_name#2689, mod_flag#1550)), false, [pos#2938, col#2939]\n",
      "      :                                                                          +- GpuProject [mod_flag#1550, orig_channel#1604, seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627]\n",
      "      :                                                                             +- GpuShuffledHashJoin [loan_id#1539L, quarter#1570], [loan_id#1603L, quarter#1629], Inner, GpuBuildRight, false\n",
      "      :                                                                                :- GpuShuffleCoalesce 536870912\n",
      "      :                                                                                :  +- GpuColumnarExchange gpuhashpartitioning(loan_id#1539L, quarter#1570, 192), ENSURE_REQUIREMENTS, [id=#1121]\n",
      "      :                                                                                :     +- GpuProject [quarter#1570, loan_id#1539L, mod_flag#1550]\n",
      "      :                                                                                :        +- GpuRowToColumnar targetsize(536870912)\n",
      "      :                                                                                :           +- SortMergeJoin [quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint)], [quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L], LeftOuter\n",
      "      :                                                                                :              :- *(2) Sort [quarter#1570 ASC NULLS FIRST, loan_id#1539L ASC NULLS FIRST, cast(timestamp_year#2417 as bigint) ASC NULLS FIRST, cast(timestamp_month#2381 as bigint) ASC NULLS FIRST], false, 0\n",
      "      :                                                                                :              :  +- Exchange hashpartitioning(quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint), 192), ENSURE_REQUIREMENTS, [id=#1080]\n",
      "      :                                                                                :              :     +- *(1) Project [loan_id#1539L, mod_flag#1550, quarter#1570, month(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2381, year(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2417]\n",
      "      :                                                                                :              :        +- GpuColumnarToRow false\n",
      "      :                                                                                :              :           +- GpuFilter (gpuisnotnull(loan_id#1539L) AND gpuisnotnull(quarter#1570)), true\n",
      "      :                                                                                :              :              +- GpuFileGpuScan parquet [loan_id#1539L,monthly_reporting_period#1540,mod_flag#1550,quarter#1570] Batched: true, DataFilters: [isnotnull(loan_id#1539L), isnotnull(quarter#1570)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,mod_flag:string,quarter:string>\n",
      "      :                                                                                :              +- GpuColumnarToRow false\n",
      "      :                                                                                :                 +- GpuSort [quarter#2484 ASC NULLS FIRST, loan_id#2453L ASC NULLS FIRST, timestamp_year#2307L ASC NULLS FIRST, timestamp_month#2336L ASC NULLS FIRST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "      :                                                                                :                    +- GpuShuffleCoalesce 536870912\n",
      "      :                                                                                :                       +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L, 192), ENSURE_REQUIREMENTS, [id=#1114]\n",
      "      :                                                                                :                          +- GpuRowToColumnar targetsize(536870912)\n",
      "      :                                                                                :                             +- *(6) HashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[])\n",
      "      :                                                                                :                                +- GpuColumnarToRow false\n",
      "      :                                                                                :                                   +- GpuShuffleCoalesce 536870912\n",
      "      :                                                                                :                                      +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248, 192), ENSURE_REQUIREMENTS, [id=#1107]\n",
      "      :                                                                                :                                         +- GpuHashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[]), filters=ArrayBuffer())\n",
      "      :                                                                                :                                            +- GpuRowToColumnar targetsize(536870912)\n",
      "      :                                                                                :                                               +- *(5) Project [quarter#2484, FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) AS josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, loan_id#2453L, month_y#2248]\n",
      "      :                                                                                :                                                  +- *(5) Filter (isnotnull(FLOOR((cast(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast((month_y#2248 - 1) as bigint)) as double) / 12.0))) AND isnotnull(CASE WHEN (pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) = 0) THEN 12 ELSE pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) END))\n",
      "      :                                                                                :                                                     +- GpuColumnarToRow false\n",
      "      :                                                                                :                                                        +- GpuGenerate gpuexplode([0,1,2,3,4,5,6,7,8,9,10,11]), [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997], false, [month_y#2248]\n",
      "      :                                                                                :                                                           +- GpuProject [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997]\n",
      "      :                                                                                :                                                              +- GpuBroadcastHashJoin [loan_id#2453L, quarter#2484], [loan_id#2202L, quarter#2233], LeftOuter, GpuBuildRight\n",
      "      :                                                                                :                                                                 :- GpuRowToColumnar targetsize(536870912)\n",
      "      :                                                                                :                                                                 :  +- *(3) Project [quarter#2484, loan_id#2453L, month(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2051, year(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2087]\n",
      "      :                                                                                :                                                                 :     +- GpuColumnarToRow false\n",
      "      :                                                                                :                                                                 :        +- GpuFilter (gpuisnotnull(quarter#2484) AND gpuisnotnull(loan_id#2453L)), true\n",
      "      :                                                                                :                                                                 :           +- GpuFileGpuScan parquet [loan_id#2453L,monthly_reporting_period#2454,quarter#2484] Batched: true, DataFilters: [isnotnull(quarter#2484), isnotnull(loan_id#2453L)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(quarter), IsNotNull(loan_id)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,quarter:string>\n",
      "      :                                                                                :                                                                 +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[1, bigint, true], input[0, string, true]),false), [id=#1096]\n",
      "      :                                                                                :                                                                    +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[gpumax(current_loan_delinquency_status#2212), gpumin(delinquency_30#1975), gpumin(delinquency_90#1976), gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :                                                                                :                                                                       +- GpuShuffleCoalesce 536870912\n",
      "      :                                                                                :                                                                          +- GpuColumnarExchange gpuhashpartitioning(quarter#2233, loan_id#2202L, 192), ENSURE_REQUIREMENTS, [id=#1093]\n",
      "      :                                                                                :                                                                             +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[partial_gpumax(current_loan_delinquency_status#2212), partial_gpumin(delinquency_30#1975), partial_gpumin(delinquency_90#1976), partial_gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "      :                                                                                :                                                                                +- GpuRowToColumnar targetsize(536870912)\n",
      "      :                                                                                :                                                                                   +- *(4) Project [quarter#2233, loan_id#2202L, current_loan_delinquency_status#2212, CASE WHEN (current_loan_delinquency_status#2212 >= 1) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_30#1975, CASE WHEN (current_loan_delinquency_status#2212 >= 3) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_90#1976, CASE WHEN (current_loan_delinquency_status#2212 >= 6) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_180#1977]\n",
      "      :                                                                                :                                                                                      +- GpuColumnarToRow false\n",
      "      :                                                                                :                                                                                         +- GpuFilter (gpuisnotnull(loan_id#2202L) AND gpuisnotnull(quarter#2233)), true\n",
      "      :                                                                                :                                                                                            +- GpuFileGpuScan parquet [loan_id#2202L,monthly_reporting_period#2203,current_loan_delinquency_status#2212,quarter#2233] Batched: true, DataFilters: [isnotnull(loan_id#2202L), isnotnull(quarter#2233)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,current_loan_delinquency_status:int,quarter...\n",
      "      :                                                                                +- GpuColumnarExchange gpuhashpartitioning(loan_id#1603L, quarter#1629, 192), ENSURE_REQUIREMENTS, [id=#1130]\n",
      "      :                                                                                   +- GpuProject [loan_id#1603L, orig_channel#1604, gpucoalesce(to_seller_name#2570, seller_name#1605) AS seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, quarter#1629]\n",
      "      :                                                                                      +- GpuShuffledHashJoin [seller_name#1605], [from_seller_name#2569], LeftOuter, GpuBuildRight, false\n",
      "      :                                                                                         :- GpuShuffleCoalesce 536870912\n",
      "      :                                                                                         :  +- GpuColumnarExchange gpuhashpartitioning(seller_name#1605, 192), ENSURE_REQUIREMENTS, [id=#862]\n",
      "      :                                                                                         :     +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :                                                                                         :        +- GpuFilter (gpuisnotnull(loan_id#1603L) AND gpuisnotnull(quarter#1629)), true\n",
      "      :                                                                                         :           +- GpuFileGpuScan parquet [loan_id#1603L,orig_channel#1604,seller_name#1605,first_home_buyer#1616,loan_purpose#1617,property_type#1618,occupancy_status#1620,property_state#1621,product_type#1624,relocation_mortgage_indicator#1627,quarter#1629] Batched: true, DataFilters: [isnotnull(loan_id#1603L), isnotnull(quarter#1629)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/acq], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,orig_channel:string,seller_name:string,first_home_buyer:string,loan_purpose...\n",
      "      :                                                                                         +- GpuColumnarExchange gpuhashpartitioning(from_seller_name#2569, 192), ENSURE_REQUIREMENTS, [id=#1127]\n",
      "      :                                                                                            +- GpuCoalesceBatches targetsize(536870912)\n",
      "      :                                                                                               +- GpuFilter gpuisnotnull(from_seller_name#2569), true\n",
      "      :                                                                                                  +- GpuRowToColumnar targetsize(536870912)\n",
      "      :                                                                                                     +- *(7) Scan ExistingRDD[from_seller_name#2569,to_seller_name#2570]\n",
      "      +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true]),false), [id=#3757]\n",
      "         +- GpuProject [data#2945 AS mod_flag#4855, id#2956]\n",
      "            +- GpuCoalesceBatches targetsize(536870912)\n",
      "               +- GpuFilter ((column_id#2942 = 9) AND gpuisnotnull(data#2945)), true\n",
      "                  +- GpuRowToColumnar targetsize(536870912)\n",
      "                     +- InMemoryTableScan [column_id#2942, data#2945, id#2956], [(column_id#2942 = 9), isnotnull(data#2945)]\n",
      "                           +- InMemoryRelation [column_id#2942, data#2945, id#2956], StorageLevel(disk, memory, deserialized, 1 replicas)\n",
      "                                 +- GpuColumnarToRow false\n",
      "                                    +- GpuProject [column_id#2942, data#2945, id#2956]\n",
      "                                       +- GpuRunningWindow [column_id#2942, data#2945, count#2951L, gpurownumber$() gpuwindowspecdefinition(column_id#2942, count#2951L DESC NULLS LAST, gpuspecifiedwindowframe(RowFrame, gpuspecialframeboundary(unboundedpreceding$()), gpuspecialframeboundary(currentrow$()))) AS id#2956], [column_id#2942], [count#2951L DESC NULLS LAST]\n",
      "                                          +- GpuSort [column_id#2942 ASC NULLS FIRST, count#2951L DESC NULLS LAST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "                                             +- GpuShuffleCoalesce 536870912\n",
      "                                                +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, 192), ENSURE_REQUIREMENTS, [id=#1141]\n",
      "                                                   +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[gpucount(1)]), filters=ArrayBuffer(None))\n",
      "                                                      +- GpuShuffleCoalesce 536870912\n",
      "                                                         +- GpuColumnarExchange gpuhashpartitioning(column_id#2942, data#2945, 192), ENSURE_REQUIREMENTS, [id=#1138]\n",
      "                                                            +- GpuHashAggregate(keys=[column_id#2942, data#2945], functions=[partial_gpucount(1)]), filters=ArrayBuffer(None))\n",
      "                                                               +- GpuProject [pos#2938 AS column_id#2942, col#2939 AS data#2945]\n",
      "                                                                  +- GpuCoalesceBatches targetsize(536870912)\n",
      "                                                                     +- GpuFilter gpuisnotnull(col#2939), true\n",
      "                                                                        +- GpuGenerate gpuposexplode(array(orig_channel#1604, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, seller_name#2689, mod_flag#1550)), false, [pos#2938, col#2939]\n",
      "                                                                           +- GpuProject [mod_flag#1550, orig_channel#1604, seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627]\n",
      "                                                                              +- GpuShuffledHashJoin [loan_id#1539L, quarter#1570], [loan_id#1603L, quarter#1629], Inner, GpuBuildRight, false\n",
      "                                                                                 :- GpuShuffleCoalesce 536870912\n",
      "                                                                                 :  +- GpuColumnarExchange gpuhashpartitioning(loan_id#1539L, quarter#1570, 192), ENSURE_REQUIREMENTS, [id=#1121]\n",
      "                                                                                 :     +- GpuProject [quarter#1570, loan_id#1539L, mod_flag#1550]\n",
      "                                                                                 :        +- GpuRowToColumnar targetsize(536870912)\n",
      "                                                                                 :           +- SortMergeJoin [quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint)], [quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L], LeftOuter\n",
      "                                                                                 :              :- *(2) Sort [quarter#1570 ASC NULLS FIRST, loan_id#1539L ASC NULLS FIRST, cast(timestamp_year#2417 as bigint) ASC NULLS FIRST, cast(timestamp_month#2381 as bigint) ASC NULLS FIRST], false, 0\n",
      "                                                                                 :              :  +- Exchange hashpartitioning(quarter#1570, loan_id#1539L, cast(timestamp_year#2417 as bigint), cast(timestamp_month#2381 as bigint), 192), ENSURE_REQUIREMENTS, [id=#1080]\n",
      "                                                                                 :              :     +- *(1) Project [loan_id#1539L, mod_flag#1550, quarter#1570, month(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2381, year(cast(gettimestamp(monthly_reporting_period#1540, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2417]\n",
      "                                                                                 :              :        +- GpuColumnarToRow false\n",
      "                                                                                 :              :           +- GpuFilter (gpuisnotnull(loan_id#1539L) AND gpuisnotnull(quarter#1570)), true\n",
      "                                                                                 :              :              +- GpuFileGpuScan parquet [loan_id#1539L,monthly_reporting_period#1540,mod_flag#1550,quarter#1570] Batched: true, DataFilters: [isnotnull(loan_id#1539L), isnotnull(quarter#1570)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,mod_flag:string,quarter:string>\n",
      "                                                                                 :              +- GpuColumnarToRow false\n",
      "                                                                                 :                 +- GpuSort [quarter#2484 ASC NULLS FIRST, loan_id#2453L ASC NULLS FIRST, timestamp_year#2307L ASC NULLS FIRST, timestamp_month#2336L ASC NULLS FIRST], false, com.nvidia.spark.rapids.OutOfCoreSort$@163d9f7d\n",
      "                                                                                 :                    +- GpuShuffleCoalesce 536870912\n",
      "                                                                                 :                       +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, timestamp_year#2307L, timestamp_month#2336L, 192), ENSURE_REQUIREMENTS, [id=#1114]\n",
      "                                                                                 :                          +- GpuRowToColumnar targetsize(536870912)\n",
      "                                                                                 :                             +- *(6) HashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[])\n",
      "                                                                                 :                                +- GpuColumnarToRow false\n",
      "                                                                                 :                                   +- GpuShuffleCoalesce 536870912\n",
      "                                                                                 :                                      +- GpuColumnarExchange gpuhashpartitioning(quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248, 192), ENSURE_REQUIREMENTS, [id=#1107]\n",
      "                                                                                 :                                         +- GpuHashAggregate(keys=[quarter#2484, loan_id#2453L, josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, month_y#2248], functions=[]), filters=ArrayBuffer())\n",
      "                                                                                 :                                            +- GpuRowToColumnar targetsize(536870912)\n",
      "                                                                                 :                                               +- *(5) Project [quarter#2484, FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) AS josh_mody_n#2264L, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997, loan_id#2453L, month_y#2248]\n",
      "                                                                                 :                                                  +- *(5) Filter (isnotnull(FLOOR((cast(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast((month_y#2248 - 1) as bigint)) as double) / 12.0))) AND isnotnull(CASE WHEN (pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) = 0) THEN 12 ELSE pmod(((24000 + (FLOOR((cast(((((timestamp_year#2087 * 12) + timestamp_month#2051) - 24000) - month_y#2248) as double) / 12.0)) * 12)) + cast(month_y#2248 as bigint)), 12) END))\n",
      "                                                                                 :                                                     +- GpuColumnarToRow false\n",
      "                                                                                 :                                                        +- GpuGenerate gpuexplode([0,1,2,3,4,5,6,7,8,9,10,11]), [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997], false, [month_y#2248]\n",
      "                                                                                 :                                                           +- GpuProject [loan_id#2453L, quarter#2484, timestamp_month#2051, timestamp_year#2087, ever_30#2004, ever_90#2005, ever_180#2006, delinquency_30#1993, delinquency_90#1995, delinquency_180#1997]\n",
      "                                                                                 :                                                              +- GpuBroadcastHashJoin [loan_id#2453L, quarter#2484], [loan_id#2202L, quarter#2233], LeftOuter, GpuBuildRight\n",
      "                                                                                 :                                                                 :- GpuRowToColumnar targetsize(536870912)\n",
      "                                                                                 :                                                                 :  +- *(3) Project [quarter#2484, loan_id#2453L, month(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_month#2051, year(cast(gettimestamp(monthly_reporting_period#2454, MM/dd/yyyy, Some(America/Los_Angeles), false) as date)) AS timestamp_year#2087]\n",
      "                                                                                 :                                                                 :     +- GpuColumnarToRow false\n",
      "                                                                                 :                                                                 :        +- GpuFilter (gpuisnotnull(quarter#2484) AND gpuisnotnull(loan_id#2453L)), true\n",
      "                                                                                 :                                                                 :           +- GpuFileGpuScan parquet [loan_id#2453L,monthly_reporting_period#2454,quarter#2484] Batched: true, DataFilters: [isnotnull(quarter#2484), isnotnull(loan_id#2453L)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(quarter), IsNotNull(loan_id)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,quarter:string>\n",
      "                                                                                 :                                                                 +- GpuBroadcastExchange HashedRelationBroadcastMode(List(input[1, bigint, true], input[0, string, true]),false), [id=#1096]\n",
      "                                                                                 :                                                                    +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[gpumax(current_loan_delinquency_status#2212), gpumin(delinquency_30#1975), gpumin(delinquency_90#1976), gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "                                                                                 :                                                                       +- GpuShuffleCoalesce 536870912\n",
      "                                                                                 :                                                                          +- GpuColumnarExchange gpuhashpartitioning(quarter#2233, loan_id#2202L, 192), ENSURE_REQUIREMENTS, [id=#1093]\n",
      "                                                                                 :                                                                             +- GpuHashAggregate(keys=[quarter#2233, loan_id#2202L], functions=[partial_gpumax(current_loan_delinquency_status#2212), partial_gpumin(delinquency_30#1975), partial_gpumin(delinquency_90#1976), partial_gpumin(delinquency_180#1977)]), filters=ArrayBuffer(None, None, None, None))\n",
      "                                                                                 :                                                                                +- GpuRowToColumnar targetsize(536870912)\n",
      "                                                                                 :                                                                                   +- *(4) Project [quarter#2233, loan_id#2202L, current_loan_delinquency_status#2212, CASE WHEN (current_loan_delinquency_status#2212 >= 1) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_30#1975, CASE WHEN (current_loan_delinquency_status#2212 >= 3) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_90#1976, CASE WHEN (current_loan_delinquency_status#2212 >= 6) THEN cast(gettimestamp(monthly_reporting_period#2203, MM/dd/yyyy, Some(America/Los_Angeles), false) as date) END AS delinquency_180#1977]\n",
      "                                                                                 :                                                                                      +- GpuColumnarToRow false\n",
      "                                                                                 :                                                                                         +- GpuFilter (gpuisnotnull(loan_id#2202L) AND gpuisnotnull(quarter#2233)), true\n",
      "                                                                                 :                                                                                            +- GpuFileGpuScan parquet [loan_id#2202L,monthly_reporting_period#2203,current_loan_delinquency_status#2212,quarter#2233] Batched: true, DataFilters: [isnotnull(loan_id#2202L), isnotnull(quarter#2233)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/perf], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,monthly_reporting_period:string,current_loan_delinquency_status:int,quarter...\n",
      "                                                                                 +- GpuColumnarExchange gpuhashpartitioning(loan_id#1603L, quarter#1629, 192), ENSURE_REQUIREMENTS, [id=#1130]\n",
      "                                                                                    +- GpuProject [loan_id#1603L, orig_channel#1604, gpucoalesce(to_seller_name#2570, seller_name#1605) AS seller_name#2689, first_home_buyer#1616, loan_purpose#1617, property_type#1618, occupancy_status#1620, property_state#1621, product_type#1624, relocation_mortgage_indicator#1627, quarter#1629]\n",
      "                                                                                       +- GpuShuffledHashJoin [seller_name#1605], [from_seller_name#2569], LeftOuter, GpuBuildRight, false\n",
      "                                                                                          :- GpuShuffleCoalesce 536870912\n",
      "                                                                                          :  +- GpuColumnarExchange gpuhashpartitioning(seller_name#1605, 192), ENSURE_REQUIREMENTS, [id=#862]\n",
      "                                                                                          :     +- GpuCoalesceBatches targetsize(536870912)\n",
      "                                                                                          :        +- GpuFilter (gpuisnotnull(loan_id#1603L) AND gpuisnotnull(quarter#1629)), true\n",
      "                                                                                          :           +- GpuFileGpuScan parquet [loan_id#1603L,orig_channel#1604,seller_name#1605,first_home_buyer#1616,loan_purpose#1617,property_type#1618,occupancy_status#1620,property_state#1621,product_type#1624,relocation_mortgage_indicator#1627,quarter#1629] Batched: true, DataFilters: [isnotnull(loan_id#1603L), isnotnull(quarter#1629)], Format: Parquet, Location: InMemoryFileIndex[file:/local/saralihalli/HOME/mortgage/acq], PartitionFilters: [], PushedFilters: [IsNotNull(loan_id), IsNotNull(quarter)], ReadSchema: struct<loan_id:bigint,orig_channel:string,seller_name:string,first_home_buyer:string,loan_purpose...\n",
      "                                                                                          +- GpuColumnarExchange gpuhashpartitioning(from_seller_name#2569, 192), ENSURE_REQUIREMENTS, [id=#1127]\n",
      "                                                                                             +- GpuCoalesceBatches targetsize(536870912)\n",
      "                                                                                                +- GpuFilter gpuisnotnull(from_seller_name#2569), true\n",
      "                                                                                                   +- GpuRowToColumnar targetsize(536870912)\n",
      "                                                                                                      +- *(7) Scan ExistingRDD[from_seller_name#2569,to_seller_name#2570]\n",
      "\n",
      "\n",
      "None\n",
      "249.0352599620819\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# run main function to process data\n",
    "out = run_mortgage(spark, perf, acq)\n",
    "\n",
    "# save processed data\n",
    "out.write.parquet(output_path, mode='overwrite')\n",
    "\n",
    "# save processed data\n",
    "if save_train_eval_dataset:\n",
    "    etlDf = spark.read.parquet(output_path)\n",
    "\n",
    "    # split 80% for training, 20% for test\n",
    "    splits = etlDf.randomSplit([0.8, 0.2])\n",
    "\n",
    "    splits[0].write.format('parquet').save(output_path_train, mode=\"overwrite\")\n",
    "    splits[1].write.format('parquet').save(output_path_eval, mode=\"overwrite\")\n",
    "\n",
    "# print explain and time\n",
    "print(out.explain())\n",
    "end = time.time()\n",
    "print(end - start)\n",
    "spark.stop()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  },
  "name": "gpu-mortgage",
  "notebookId": 4440374682851873
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
